Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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?
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 @v-xinruzhu-msft ,
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.
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.
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.
User | Count |
---|---|
89 | |
88 | |
75 | |
67 | |
58 |
User | Count |
---|---|
137 | |
110 | |
92 | |
84 | |
69 |