Forum Discussion
Showing Top 3 Counts in Matrix Table
I have this Matrix table that uses Body Grouping for Rows, Cause Grouping for Columns and Values is Total Count. What do I need to create to show only the Top 3 Causes For the Top 3 Body Grouping. So we would see Multiple Body Parts with Strain 25, STF 22 and NC 9, Back with Strain 60, STF 2 and MVA 2, Hand with Cut/Puncture 8, Caught between 4 and Assault 3?
14 Replies
Should have stated Top 3. Sorry
Hi bdehning ,
Here is my sample data:Body Grouping Cause Grouping Multiple Body Parts Animal Multiple Body Parts Assault Multiple Body Parts CTS Multiple Body Parts Exposure Multiple Body Parts MVA Multiple Body Parts NC Multiple Body Parts STF Multiple Body Parts Strain Multiple Body Parts Step on Multiple Body Parts Struckby Multiple Body Parts Strain Multiple Body Parts Strain Multiple Body Parts Strain Multiple Body Parts Strain Multiple Body Parts STF Multiple Body Parts STF Multiple Body Parts STF Multiple Body Parts NC Multiple Body Parts NC Back Assault Back CTS Back MVA Back STF Back Strain Back MVA Back STF Back Strain Back Strain Back Strain Back Strain Hand Animal Hand Assault Hand Burn/Scald Hand Caught between Hand Cut/Puncture Hand NC Hand STF Hand Strain Hand Assault Hand Assault Hand Cut/Puncture Hand Cut/Puncture Hand Cut/Puncture Hand Cut/Puncture Hand Cut/Puncture Hand Caught between Hand Caught between Hand Caught between
First you can create this DAX:Count_Cause = COUNT('Table'[Cause Grouping])Then, add another DAX to rank:
_RANK = RANKX(ALLEXCEPT('Table', 'Table'[Body Grouping]), [Count_Cause], , DESC, Skip)However, after testing, the use of measure as a filter for matrices is limited in that it works for table visual objects but not for matrices.
So I can offer you a workaround, i.e. use conditional format in the matrix. In the Values of the Matrix > Conditional formatting > Background color:
The final output is as below:
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Thanks for trying, but that puts me about the same place that I already was at. Trying to not show causes that are not included in the Top 3?
I now am using my own Count which I laready had instaed of Count_Cause and I get correct numbers for each Row for Body Grouping and Cause Grouping.
I switched out _RANK =
RANKX(ALLEXCEPT('BodyPart', 'BodyPart'[Body Grouping]), 'LossRun'[Count of Total Gross Incurred], , DESC, Skip)
I stiill get all 1's under _RANK in my table?
What may be wrong?
