Forum Discussion
bdehning
Post Prodigy
Post ProdigyShowing 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. ...
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.
bdehningPost Prodigy
Post ProdigyThanks 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?