Forum Discussion

Marcin's avatar
Marcin
Helper V
7 years ago

Separate into two matrixes

Hi,

 

I have prepared small demo data like this :

 

UnitKPIValue
Unit1KPI112
Unit1KPI211
Unit1KPI315
Unit1KPI416
Unit1KPI522
Unit1KPI656
Unit1KPI745
Unit1KPI833
Unit1KPI922
Unit1KPI1067
Unit2KPI114
Unit2KPI224
Unit2KPI345
Unit2KPI434
Unit2KPI533
Unit2KPI645
Unit2KPI733
Unit2KPI865

 

I am looking for solution when I will be able to create report with two matrixes with max 5 rows each. 

Each Unit can have max 10 KPIs . 

I would like to present 5 kpis for one matrix and 5 or less for second matrix.  

 

So for example , when I select Unit 1 i would like to get this:

 

When I select Unit 2 I would like to get like this :

 

 

I would like to make it dinamically filtered as I can get some new KPIs after refresh dataset, but always max for one unit is 10. 

 

I will be grateful for help.

 

 

Need Help

3 Replies

  • Marcin's avatar
    Marcin
    Helper V
    7 years ago

    I am thinkig about some kind of index column like this which I have created manually and then use it for filtering matrix as <= 5 and > 5 . 

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Marcin,

     

    You can try to use following calculate column formula to get ranking based on current unit. Then you can add a topn filter on rank column to choose top 5 ranking based on its value.

    Rank =
COUNTROWS (
    FILTER (
        Table3,
        Table3[Unit] = EARLIER ( [Unit] )
            && [Value] > EARLIER ( Table3[Value] )
    )
)
    + 1

     

    Regards,

    Xiaoxin Sheng

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    hii Marcin

      

     calculate the RANK which is gives you the separte index number for UNIT1 and UNIT2 then use the visual filter option for Rank <=5 and Rank>5

     

     

    Rank = RANKX (ALL (Sheet1[KPI]), Sheet1[sum_value])

     

    Thanks and Reg

    pr137