Forum Discussion
Total Not Calculating Right in Matrix
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.
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?
5 Replies
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.
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!
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.
