Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Top N with multiple drill down.

I have the following table:

 

   Name       Number Letter   Value

Carlos1A80
Carlos1B70
Carlos2A60
Carlos2B50
Henrique1A40
Henrique1B30
Henrique2A20
Henrique2B10
Macias1A10
Macias1B9
Macias2A8
Macias2B7

 

And the folowing matrix visual and slicer:

 

 

To create the rank, i used this formua:

 

 

Rank = 
IF(
    ISFILTERED(Table1[Level Letter]);
    RANKX(ALLSELECTED(Table1[Level Letter]); CALCULATE(SUM(Table1[Value])); ; DESC; Dense);
    IF(
        ISFILTERED(Table1[Level Number]);
        RANKX(ALLSELECTED(Table1[Level Number]); CALCULATE(SUM(Table1[Value])); ; DESC; Dense);
        RANKX(ALLSELECTED(Table1[Level Name]); CALCULATE(SUM(Table1[Value])); ; DESC; Dense)
    )
)

 

 

And get the selected value of the top N slicer I am using this code:

 

SelectedValue = 
IF(
    HASONEVALUE('TopN'[Show N]);
    SELECTEDVALUE('TopN'[ID]);
    -1
)

 

Now I need a way to show only the Top N values and subvalues. For example:

 

 

Show Top 1:

 

 

 

Show Top 2:

 

But I can not find a way to do so.