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
bluetronics
Helper III
Helper III

Cummulative Percentage by group

Hello All,

I would like to get the result the same as tabel below:

I can get total by customer, rank and % by Rank. but I donlt know how to get "cummulative % by rank". Pls. instruct me how to get it. Thanks.

CustomersalesTotal by customerRank% by Rankcummulative % by Rank
A10003000150%50%
B200900315%82%
C300600410%92%
D50050058%100%
E10001000217%67%
A20003000150%50%
B700900315%82%
C300600410%92%
Total6000    
1 ACCEPTED SOLUTION
LivioLanzo
Solution Sage
Solution Sage

@bluetronics

 

You can do it like this: 

 

VAR CurSales = CALCULATE( 
                SUM( Data[sales] ), 
                ALLEXCEPT( Data, Data[Customer] ) 
              )
RETURN
DIVIDE(
    CALCULATE(
        SUM( Data[sales] ),
        FILTER(
                ALL( Data[Customer] ),
                CALCULATE( 
                    SUM( Data[sales] ), 
                    ALLEXCEPT( Data, Data[Customer] ) 
                ) >= CurSales
            ),
        ALL( Data )
    ),
    SUM( Data[sales] )
)

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

View solution in original post

5 REPLIES 5
LivioLanzo
Solution Sage
Solution Sage

@bluetronics

 

You can do it like this: 

 

VAR CurSales = CALCULATE( 
                SUM( Data[sales] ), 
                ALLEXCEPT( Data, Data[Customer] ) 
              )
RETURN
DIVIDE(
    CALCULATE(
        SUM( Data[sales] ),
        FILTER(
                ALL( Data[Customer] ),
                CALCULATE( 
                    SUM( Data[sales] ), 
                    ALLEXCEPT( Data, Data[Customer] ) 
                ) >= CurSales
            ),
        ALL( Data )
    ),
    SUM( Data[sales] )
)

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

@LivioLanzo

Thank you for your feedback. I did the script but there is syntex error. Did I something wrong?

Cur slaes.jpg

Hi bluetronics,

 

You should add a column name before the formula.(e.g.:

cummulative % by Rank_ =
VAR CurSales =
......
......

 

Regards,

Jimmy Tao

Thanks a lot! it works. 🙂

bluetronics
Helper III
Helper III

I found the queations what I would like to know. But I still don't understand how to do it.

Pls. help me someboy know the answer.

https://community.powerbi.com/t5/Desktop/Running-total-with-ranking/m-p/323103#M144010

Thanks in advance.

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.