Urgent please - Delta on 2 scenarios

[Anonymous]

Hi, 

I have the data in a table called RawData in this format:

Scenario	Function	Metric
A	AA	10
A	AA	20
A	AB	10
B	AA	30
B	AB	40
B	AB	10

and I will want to retrieve 2 matrix tables displayed like this:

One that is based on a slicer where scenario = A on this format

Function	Sum of scenario A	Delta of selected scenarios in selected slicers
AA	30	0
AB	10	-40

One that is based on a slicer where scenario = B on this format

	Sum of scenario B
AA	30
AB	50

Can you, please, support on this?

Many thanks!

[v-jianboli-msft]

Hi @Anonymous ,

Please try:

Measure =
IF (
    ISINSCOPE ( 'Table'[Scenario] ),
    SUM ( 'Table'[Metric] ),
    CALCULATE ( SUM ( 'Table'[Metric] ), FILTER ( 'Table', [Scenario] = "A" ) )
        - CALCULATE ( SUM ( 'Table'[Metric] ), FILTER ( 'Table', [Scenario] = "B" ) )
)

Output:

Best Regards,

Jianbo Li

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

[Anonymous]

Unfortunately, the user wants what I mentioned in the initial msg, 2 slicers with 2 tables. The intial solution you have provided was doing that, except that the column total for delta was not ok..

[v-jianboli-msft · Accepted solution]

Hi @Anonymous ,

Sorry I misunderstood your needs before, please try this MEASURE:

Delta of selected scenarios in selected slicers = 
var _a = SUMMARIZE('Table','Table'[Function],"Metric",SUM('Table'[Metric]),"Metric2",CALCULATE(SUM('Table'[Metric]),FILTER(ALL('Table'),[Function]=EARLIER('Table'[Function])&&[Scenario] =SELECTEDVALUE('For slicer'[Scenario]))))
return 
SUMX(_a,[Metric]-[Metric2])

 Output:

Best Regards,

Jianbo Li

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

[Anonymous]

Thank you very much, this is perfect!!

[v-jianboli-msft]

Hi @Anonymous ,

Please try:

First create a new table for slcier:

Then apply the measure:

Delta of selected scenarios in selected slicers =
SUM ( 'Table'[Metric] )
    - CALCULATE (
        SUM ( 'Table'[Metric] ),
        FILTER (
            ALL ( 'Table' ),
            [Function] = MAX ( 'Table'[Function] )
                && [Scenario] = SELECTEDVALUE ( 'For slicer'[Scenario] )
        )
    )

Create two slicers using 'Table'[Scenario] and 'For slicer'[Scenario] respectively

Final output:

Best Regards,

Jianbo Li

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

[Anonymous]

Hi, 

It's working, but the bottom totals are not doing delta. I mean, in your examples,  I should have - 40 and 0 on the bottom tables as grand totals.

Thank you!