Using Calculation Groups to Desensitise your data for Presentations

Have you ever put together a report that has some really cool functionality and / or you just love the aesthetics and all you want to do is show it off internally to your colleagues... but it contains confidential client information?

I know I have! And who has time these days to create a synthetic dataset that you can plug in? I certainly don't, so you end up missing the opportunity to share that knowledge.

In the following article I'll share with you a quick way to obfuscate data in a report to make it a much quicker process to get it ready for internal presentation.

Please note! You MUST ALWAYS run this passed your client first! In a lot of cases the I.P you're creating belongs solely to the client.

For this example, I'm using this sample dataset provided by Microsoft

And Tabular Editor 2

To create the above we need 3 measures:

Net Sales - A basic measure of net sales

Net Sales =
  CALCULATE(
    SUM(Sales[Amount])
    ,Sales[Status] = "Sold"
  )         

Net Sales (Budget) - An artificial budget I've created for this example

Net Sales (Budget) = 
  50000 * COUNTROWS('Calendar')        

And the % Variance between them

Net Sales Variance (Budget) (%) = 
  DIVIDE(
     [Net Sales]
    ,[Net Sales (Budget)]
    ,0)-1        

Now that we've got the foundation, we need to apply the calculation group magic!

If you've got Tabular Editor installed, it should turn up in the 'External Tools' ribbon

Fire it up !

Start by creating a new calculation group

• Right click on Tables
• Create New
• Calculation Group
• Rename it to your preference (I always prefer to prefix my Calculation Groups with 'cg' to make them easier to tell from regular tables

Create a new Calculation Item

• Expand your new calculation group
• Right click and select New Calculation Item
• Rename your new item

The Calculation Item:

SELECTEDMEASURE () * DIVIDE ( RANDBETWEEN ( 50, 150 ), 100 )        

• SELECTEDMEASURE() - Takes any measure or value on the page and passes it through to the Calculation Group
• RANDBETWEEN ( 50, 150 ) returns a random number between 50 and 150
• We DIVIDE by 100 to get it to a % Difference
• As a whole we now get a change to EVERY calculation of between 50% and 150% with a different random number for each calculation

Once we've created that, save it back to the model, click 'Refresh Now' this will populate the calculation group in the report

You will now see the calculation group in your fields list (Note the cg prefix)

Now you can pull it in to a filter ( Page / Report / Slicer / Any filter works )

No filter:

With Filter

As you can see, all the numbers have changed! But we have a problem, the budget has gone all haywire, remember this is because EVERY measure gets passed through and calculated ( this is something to bear in mind when using Calculation Groups with averages and calculated % )

So back we go to Tabular Editor and we'll create a New Calculation Item

VAR _IfStatic =
  CALCULATE ( DIVIDE ( RANDBETWEEN ( 50, 150 ), 100 ), ALL ( 'Calendar' ) )
RETURN
  SWITCH (
    TRUE()
    ,CONTAINSSTRING ( SELECTEDMEASURENAME (), "Budget" )
    ,SELECTEDMEASURE () * _IfStatic
    ,SELECTEDMEASURE () * DIVIDE ( RANDBETWEEN ( 50, 150 ), 100 )
  )        

• We create a variable _IfStatic where we just want to evaluate it once for all calculations (in this case we're calculation across dates)
• We open a SWITCH() - This allows us to evaluate all kinds of criteria and gives us a lot of opportunities to apply custom formatting or calculations
• In this case, we're just using CONTAINSSTRING() and SELECTEDMEASURENAME() to search for the word 'Budget' in the measure name itself
• If the SELECTEDMEASURENAME() does not contain 'Budget' evaluate each step

So when we return to the report and refresh, you can see the Budget is now behaving nicely

Please Note! This technique is only useful for obscuring the data for presentations or for screen shots, if you send the PBIX, the data has not been changed in the back end at all, if you show the 'Data' tab on the left-hand ribbon all the data will be actuals!

I hope you've found my technique interesting :)

Please reach out if you have any questions!

If you're looking for some further reading on how calculation groups work, I recommend working your way through this great set of articles