Forum Discussion

ilui's avatar
ilui
Frequent Visitor
6 years ago
Solved

Delta calculation based on dynamic Slicer

Hi,   I'm stuck with this New Measure field and I'd like to seek your help. We're taking Salesforce snapshot data and want to show comparison of forecast based on different snapshot date. Which sna...
Need Help
  • v-yingjl's avatar
    v-yingjl
    6 years ago

    Hi ilui ,

    Based on your description, you can create this measure:

    Forecast Diff =
VAR _max =
    CALCULATE ( MAX ( 'Table'[Snapshot Date] ), ALLSELECTED ( 'Table' ) )
VAR _min =
    CALCULATE ( MIN ( 'Table'[Snapshot Date] ), ALLSELECTED ( 'Table' ) )
VAR _maxsum =
    CALCULATE ( SUM ( 'Table'[Forecast] ), 'Table'[Snapshot Date] = _max )
VAR _minsum =
    CALCULATE ( SUM ( 'Table'[Forecast] ), 'Table'[Snapshot Date] = _min )
RETURN
    _maxsum - _minsum

    To achieve the same visual, create two matrix to use visual overlay:

     

    Attached the sample file that hopes to help you: Forecast data.pbix

     

    Best Regards,
    Yingjie Li

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

ilui's avatar
ilui
Frequent Visitor
6 years ago

v-yingjl Oh I missed the super imposed part. I'll try but I'm not sure if it'll work for my situation because my Matrix visual will have several level of drill down for people to look at the variances.

 

If measures cannot be used as a separate column in the matrix, any alternative suggestion on how to show this better?

 

Thanks a lot of your help!

tex628's avatar
tex628
Community Champion
6 years ago

You can do quite alot of workarounds in matrixes by using Selectedvalue() and blanking values. 

Here's a brief example:

Plain thable just displaying the sum of cats. No conditions.

If we want to remove specific columns in the matrix we can look from them using SELECTEDVALUE and then remove them:

Measure replacing the Blue cats with blanks()

 

 

Count_ no Blue = 
VAR Color_ = SELECTEDVALUE(Facts[Color])
return
IF( Color_ = "BLUE" , BLANK() , [Count_] )

 


Notice that the Total is still displaying 160, even through the total displayed sum is only 40. 

Just specifiy the column and value your want to identify. Use a variable with selectedvalue, and then compare that variable in an IF statment:

Removing cat 3 and cat 5 from the matrix

 

 

Count_ no Cats = 
VAR Category_ = SELECTEDVALUE(Facts[Category])
return
IF( OR( Category_  = "Cat 3" , Category_ = "Cat 5" ) , BLANK() , [Count_] )

 


Hope this helps!

Br,
J