Forum Discussion
Matrix Roll-Up Calculations: How to avoid double counts for same values across different rows?
I am struggling to enforce correct roll-up calculations for categories when some of the sub categories have the identical values (which is the correct logic that they have). To simplfy the question, I have created a simple example pbix which can be found here:
https://1drv.ms/u/s!AkT9ZjaCMHRkiMU_BOIpqzBf_3FWzg?e=nbV7f1
This is the model, I use. It has six category levels and two metrics. The problem is the metric "Actions". In a Matrix visualization, I need the metric to be de-duplicated depending on the category level that is looked at.
Example One Level View:
The total should be 58,266,490
This happens on any level where the associated category has the same values twice or more times.
What kind of measure should I use for "Actions" so that it would ignore duplicated values in the calculations in the matrix visual?
As mentioned earlier, the pbix with additional months of data can be found here: https://1drv.ms/u/s!AkT9ZjaCMHRkiMU_BOIpqzBf_3FWzg?e=nbV7f1
Hi frankGB ,
According to your description, here are my steps you can follow as a solution.
(1) My test data is the same as yours.
(2) We can create a measure.
Measure = SWITCH ( SELECTEDVALUE('Metrics'[Metric Name]), "Counts", SUMX(VALUES('Table'[Counts]),[Counts]), "Actions", SUMX(VALUES('Table'[Actions]),[Actions]) )(3) Then the result is as follows.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
This works very well. Thank you so much!
2 Replies
Hi frankGB ,
According to your description, here are my steps you can follow as a solution.
(1) My test data is the same as yours.
(2) We can create a measure.
Measure = SWITCH ( SELECTEDVALUE('Metrics'[Metric Name]), "Counts", SUMX(VALUES('Table'[Counts]),[Counts]), "Actions", SUMX(VALUES('Table'[Actions]),[Actions]) )(3) Then the result is as follows.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
This works very well. Thank you so much!