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