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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
Anonymous
Not applicable

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
Anonymous
Not applicable

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.