Cummulative Percentage by group

[bluetronics]

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.

Customer	sales	Total by customer	Rank	% by Rank	cummulative % by Rank
A	1000	3000	1	50%	50%
B	200	900	3	15%	82%
C	300	600	4	10%	92%
D	500	500	5	8%	100%
E	1000	1000	2	17%	67%
A	2000	3000	1	50%	50%
B	700	900	3	15%	82%
C	300	600	4	10%	92%
Total	6000

[LivioLanzo · Accepted solution]

@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] )
)

[bluetronics]

@LivioLanzo

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

[v-yuta-msft]

Hi bluetronics,

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

cummulative % by Rank_ =

VAR CurSales =

......

......

) 

Regards,

Jimmy Tao

[bluetronics]

Thanks a lot! it works. 🙂

[bluetronics]

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.