Forum Discussion
Solved
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.
| 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 |
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] ) )
5 Replies
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] ) )Thank you for your feedback. I did the script but there is syntex error. Did I something wrong?
Hi bluetronics,
You should add a column name before the formula.(e.g.:
cummulative % by Rank_ =VAR CurSales =............)
Regards,
Jimmy Tao
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.
