Total Not Calculating Right in Matrix

[encamped1]

Hello Community,

I'm pretty new to Power BI and still struggling with calculating totals correctly in Matrix. 

In the sample dataset, I want the "Total" column in the matrix to always represent the sum of the values displayed above it in the matrix. Instead of calculating the distinct count of the backend data based on the selected values in the "Year" and "Region" slicers, I would like it to show the actual sum. For instance, the total distinct count for the year 2017 should be 1652, not the visual representation of 693. 

Sample.pbix

I've tried with a Switch function but it wasn't working as intended. Is this something that I can achieve with it or should I use a different function? 

[Anonymous]

Hi @encamped1 

You can create two measures

Measure = SUMX(VALUES(Orders[State]),[Measure 2])
Measure 2 = DISTINCTCOUNT(Orders[Customer ID])

Then put the measure to the matrix

Best Regards!

Yolo Zhu

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

[encamped1]

Hi @Anonymous ,

thanks for the comment! It worked as it was intended but is it possible to combine the two measures that you created into one? I've tried by creating the first measure as a variable in the second measure however somehow the total row calculation was off and I had no idea what was going on. In my workbook I have dozens of measures that I need to update with this logic and I just didn't want to create another set just for this. 

thank you for the help! 

[Anonymous]

Hi @encamped1 

You can use the following measure

Measure = var a=SUMMARIZE(VALUES(Orders[State]),[State],"distinct",DISTINCTCOUNT(Orders[Customer ID]))
return SUMX(FILTER(a,[State] in VALUES(Orders[State])),[distinct])

Best Regards!

Yolo Zhu

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

[encamped1]

Hi @Anonymous 

You're the best!

One last ask - can we create a single measure that would work more dynamically? i.e. if the State realted contents in the matrix, show the sumx of the States, if a Matrix has the category field as a row, show the sumx of the sub-category based total at the bottom of the table,, etc. 

[Anonymous]

Hi @encamped1 

You can use isincope() function to judge, you can refer to the following link.

ISINSCOPE function (DAX) - DAX | Microsoft Learn

Best Regards!

Yolo Zhu

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