Showing Top 3 Counts in Matrix Table

[bdehning]

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?

[bdehning]

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?

[Anonymous]

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.

[bdehning]

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?  

[bdehning]

Should have stated Top 3. Sorry

[bdehning]

I got closer by using Body Grouping as Filter and using Top 3 by Total Count.  Now I need a measure to use as a filter to show only the the Top 3 Body Grouping.  

[bdehning]

[bdehning]

I am still stuck on this on trying trying not to show columns of Cause that are not included in the Top 3 per Body Grouping.  In this case, I should not see Animal, Burn, Scald, , Exposure, Strike, Step on and Struckby

[Anonymous]

Hi @bdehning ,

After my testing, it is unlikely that you want to act on the measure in the filter to achieve your needs, I would suggest the following approach:
Create these measures:

Count_Cause = 
COUNT('Table'[Cause Grouping])

_RANK = 
RANKX(ALLEXCEPT('Table', 'Table'[Body Grouping]), [Count_Cause], , DESC, Skip)

Output = 
IF( 
    [_RANK] <= 3,
    [Count_Cause],
    BLANK()
)

Then put the measure Output into the Value of the Matrix:

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.

[bdehning]

I am not getting what you got so far.    I am getting this so far.     I will try again b ut providing my model info below.   

My Body Grouping is in Table BodyPart
My Cause Grouping is in Table Injury Cause

BodyPart and Injury Cause are both joined to LossRun Table.

My Count_Cause =

COUNT('InjuryCause'[Cause Grouping])

My  _RANK =

RANKX(ALLEXCEPT('BodyPart', 'BodyPart'[Body Grouping]), [Count_Cause], , DESC, Skip)

My Output =

IF(

    [_RANK] <= 3,

    [Count_Cause],

    BLANK()

)

I do have _Rank has a Filter and is less than or equal to 3.   

Something is a miss on my end.   

[bdehning]

It is something with my  _RANK measure as all Body Grouping Rows show 1.

[bdehning]

Count_Cause is not providing accurate numbers either for each Body Grouping?

[bdehning]

I started to break this down so you can help.  I did create 

Total Cause Count = COUNTROWS(RELATEDTABLE(LossRun))
So now I see table with Counts for each of Rows of Body Part and Cause Grouping.  

I then tried to use 

_RANK =

RANKX(ALLEXCEPT('BodyPart', 'BodyPart'[Body Grouping]), [Total Cause Count], , DESC, Skip), but all _RANK rows show a 1.  

What do I need to correct in _RANK to make it work. 

[bdehning]

I added more data to your example in both columns and it shows Body Grouping and Cause Grouping when count is only one and starts to add more than 3 Rows.     Here is my current table.    It is not just showing TOP 3 Body Grouping now.  

[bdehning]

Here is my Table I have now for the Slicer Selection I have.   

How would your _RANK measure below change with my model? 

_RANK = 
RANKX(ALLEXCEPT('Table', 'Table'[Body Grouping]), [Count_Cause], , DESC, Skip)