cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
malekzouaridk
Helper I
Helper I

Cumulative measure sort by the biggest percentage

Hello Community,

 

I created a table in power bi desktop with two columns Product and percentage of distinct count of Number of ligns which I sorted to display values from big to small, I want to create a new column which display the cumulative percentage of number of ligns . For example here I want the first row display 5.52%, the second one 10.11% the third one  14.12%...

 

malekzouaridk_0-1637676443417.png

 

Is there a way to do it through calculated column or a measure ?

 

Thanks in advance

 

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

Hi @malekzouaridk,

Please create an index column to your table to sort your records, then you can write measure expressions with a variable table to calculate the percent of the current product. Then you can use the iterator function to apply the second aggregate on the variable table results.

Measure =
VAR currIndex =
    MAX ( Table[Index] )
VAR summary =
    SUMMARIZE ( 'Table', [Index], [Product], "Percent", [%TG] )
RETURN
    SUMX ( FILTER ( summary, [Index] <= currIndex ), [Percent] )

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

1 REPLY 1
v-shex-msft
Community Support
Community Support

Hi @malekzouaridk,

Please create an index column to your table to sort your records, then you can write measure expressions with a variable table to calculate the percent of the current product. Then you can use the iterator function to apply the second aggregate on the variable table results.

Measure =
VAR currIndex =
    MAX ( Table[Index] )
VAR summary =
    SUMMARIZE ( 'Table', [Index], [Product], "Percent", [%TG] )
RETURN
    SUMX ( FILTER ( summary, [Index] <= currIndex ), [Percent] )

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors