Forum Discussion

bdehning's avatar
bdehning
Icon for Post Prodigy rankPost Prodigy
2 years ago

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.  ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
bdehning
Icon for Post Prodigy rankPost Prodigy
2 years ago

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's avatar
    bdehning
    Icon for Post Prodigy rankPost Prodigy
    2 years ago

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

    • bdehning's avatar
      bdehning
      Icon for Post Prodigy rankPost Prodigy
      2 years ago

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

      • bdehning's avatar
        bdehning
        Icon for Post Prodigy rankPost Prodigy
        2 years ago

        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.