Aggregation per row in table

[patricia1707]

Hi,

I have table like this 

 but I would like to have table as below, so KPI id should be only once 

Or sth like this:

is it possible to do? 

[Anonymous · Accepted solution]

Hi @patricia1707 ,

Based on the information you provided, you can follow these steps below:

1.Add Index Column From 1 in Power Query

2.Add a Column

ID =

VAR _1 =

    RANKX (

        FILTER ( ALL ( 'Table' ), 'Table'[kpi_id] = EARLIER ( 'Table'[kpi_id] ) ),

        'Table'[Index],

        ,

        ASC

    )

RETURN

    IF ( _1 = 1, 'Table'[kpi_id], BLANK () )

Final output:

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

^{Best Regards,}

^{Yifan Wang}

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

[zenisekd]

Instead of table visual, use matrix visual. That should solve your problem 🙂 

it allows you to create a hierarchy in the first column...

Kudos and mark as solution appreciated 🙂

[patricia1707]

hmm unfortunately I totally can't get the effect I want using matrix because of these two fields:

but I want this