Incorrect Matrix subtotals for the measure calculated based on user defined selection in the slicer

[salil7]

Hello experts,

I have a peculiar issue which I am not able to resolve. I have the underlying data organized in the following format

Scenario	Product	Month	Quantity
1	X	Jan	100
1	X	Feb	800
1	Y	Jan	750
1	Y	Feb	200
2	X	Jan	300
2	X	Feb	450
2	Y	Jan	550
2	Y	Feb	500

I have a slicer to select the scenarios and have created a measure to calculate the difference in quantity based on any two selected scenarios. Eg. If the user selects Scenario 1 and 2 from the slicer, he should see (Scenario 2-Scenario 1)

Product	Jan	Feb
X	200	-350
Y	-200	300

Measure definition:

DeltaMeasure =

VAR Top1N =

VALUE (

CALCULATE (

CONCATENATEX (

TOPN ( 1, VALUES ( 'MST_SCENARIO'[Index] ) ),

'MST_SCENARIO'[Index],

", "

)

)

)

VAR Top2N =

VALUE (

CALCULATE (

CONCATENATEX (

TOPN ( 1, VALUES ( 'MST_SCENARIO'[Index] ), 'MST_SCENARIO'[Index], DESC ),

'MST_SCENARIO'[Index],

", "

)

)

)

VAR FirstSelectedValue =

CALCULATE ( SUMX(ScenarioComparisonData,ScenarioComparisonData[Measure]),MST_SCENARIO[Index]=Top1N)

VAR SecondSelectedValue =

CALCULATE ( SUMX(ScenarioComparisonData,ScenarioComparisonData[Measure]),MST_SCENARIO[Index]=Top2N)

RETURN

SecondSelectedValue - FirstSelectedValue

I want to visualize this in a matrix visual.

Everything works as expected when I put Product and Month in the rows and DeltaMeasure in the values. However, I see random subtotals when I put Month in columns (Everything else remains the same).

I can provide further information if required. Any help in this regard will be highly appreciated.

[salil7]

Thanks, @amitchandak  for your response. Is there a way to extend this logic to multiple columns? I have attached the screenshot of my Power BI desktop. In the top visual, you will see that I have Family, Origin, Destination, Mode and Product defined in the Rows. Period in the column and DeltaMeasure in the values. In the bottom visual, I have exactly the same row hierarchy (+Scenario), Period in columns and Measure in the values.

I expect the difference between the row subtotals of the bottom visual to match the values in the top visual period by period. As you will observe, the difference in the row subtotals for the two rows in the bottom visual (15,185,274-15,071,377) exactly matches the grand total in the top visual 113,897. However, if you look at the values in individual periods: Period 202007 as an example - I expect the top visual to populate 836,791 - 874,763 = -37,792 as the value. But what I see is -16,032.

Any pointers on how can I achieve this? Many thanks for your help.

[Anonymous · Accepted solution]

Hi @salil7 ,

According to your description and screenshots, it seems the data display in the bottom visual is not correct every row.... Could you please provide the related setting of bottom visual in Fields pane? And please provide some sample data in table ScenarioComparisonData and MST_SCENARIO if it is convinent in order to make troubleshooting. Thank you.

Best Regards

[salil7]

Hi @Anonymous ,

You are right. I realized that there was a visual level filter for the top visual where I had a put a condition that DeltaMeasure should not be equal to zero. It started showing me the right numbers when I removed that filter. Thank you for the pointers! 

[amitchandak]

@salil7 , Change return like 

return

sumx(values(ScenarioComparisonData[product]), SecondSelectedValue - FirstSelectedValue)