Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
patricia1707
Regular Visitor

Aggregation per row in table

Hi,

I have table like this 

patricia1707_0-1704192072748.png

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

patricia1707_2-1704192408124.png

Or sth like this:

patricia1707_3-1704195028909.png


is it possible to do? 

1 ACCEPTED SOLUTION
v-yifanw-msft
Community Support
Community Support

Hi @patricia1707 ,

 

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

 

1.Add Index Column From 1 in Power Query

 

vyifanwmsft_0-1704270277747.png

 

 

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:

vyifanwmsft_1-1704270277750.png

 

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.

View solution in original post

3 REPLIES 3
v-yifanw-msft
Community Support
Community Support

Hi @patricia1707 ,

 

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

 

1.Add Index Column From 1 in Power Query

 

vyifanwmsft_0-1704270277747.png

 

 

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:

vyifanwmsft_1-1704270277750.png

 

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
Super User
Super User

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

zenisekd_0-1704195558720.png

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



Kudos and mark as solution appreciated 🙂

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

patricia1707_0-1704203008868.pngpatricia1707_1-1704203084362.png

but I want this

patricia1707_3-1704203152187.png

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors