Separate into two matrixes

[Marcin]

Hi,

I have prepared small demo data like this :

Unit	KPI	Value
Unit1	KPI1	12
Unit1	KPI2	11
Unit1	KPI3	15
Unit1	KPI4	16
Unit1	KPI5	22
Unit1	KPI6	56
Unit1	KPI7	45
Unit1	KPI8	33
Unit1	KPI9	22
Unit1	KPI10	67
Unit2	KPI1	14
Unit2	KPI2	24
Unit2	KPI3	45
Unit2	KPI4	34
Unit2	KPI5	33
Unit2	KPI6	45
Unit2	KPI7	33
Unit2	KPI8	65

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.

[Anonymous]

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

[v-shex-msft]

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

[Marcin]

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 .