Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I'm trying to extract the top % of products by sales for each customer. the script below returns the cumulative percentage but not in order. What I want is to sort them from high to low and then run the code below.
Solved! Go to Solution.
output
measures :
ranking measure :
rnk =
RANKX(
ALLSELECTED(tbl1[Product]),CALCULATE(sum(tbl1[monthly sales])),,DESC)
measure cumul :
cumulative sum =
var current_ranking = [rnk]
var s =
CALCULATE(
SUM(tbl1[monthly sales]),
FILTER(
ALLSELECTED(tbl1[Product]),
[rnk] <=current_ranking
)
)
return s
cumult%
cumult % =
[cumulative sum]
/
CALCULATE(SUM(tbl1[monthly sales]), ALLSELECTED(tbl1[Product])
)
if my ansswer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠
How to provide sample data in the Power BI Forum
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
How to Get Your Question Answered Quickly
https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...
Customer | Product | monthly sales | Sales% | Comulative% |
CustA | Prod1 | 236 | 22% | 22% |
CustA | Prod2 | 185 | 17% | 39% |
CustA | Prod3 | 181 | 17% | 55% |
CustA | Prod4 | 142 | 13% | 68% |
CustA | Prod5 | 136 | 12% | 81% |
CustA | Prod6 | 115 | 11% | 91% |
CustA | Prod7 | 95 | 9% | 100% |
CustB | Prod1 | 92 | 19% | 19% |
CustB | Prod2 | 86 | 18% | 36% |
CustB | Prod3 | 79 | 16% | 53% |
CustB | Prod4 | 79 | 16% | 69% |
CustB | Prod5 | 78 | 16% | 85% |
CustB | Prod6 | 75 | 15% | 100% |
I cant share teh data model. the data is sensitive. But this is more or less what i have. I want to calculate the running total for all product for each customer sorted from high to low
output
measures :
ranking measure :
rnk =
RANKX(
ALLSELECTED(tbl1[Product]),CALCULATE(sum(tbl1[monthly sales])),,DESC)
measure cumul :
cumulative sum =
var current_ranking = [rnk]
var s =
CALCULATE(
SUM(tbl1[monthly sales]),
FILTER(
ALLSELECTED(tbl1[Product]),
[rnk] <=current_ranking
)
)
return s
cumult%
cumult % =
[cumulative sum]
/
CALCULATE(SUM(tbl1[monthly sales]), ALLSELECTED(tbl1[Product])
)
if my ansswer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠
This is a list of all the product for a certain customer. The sales column is already sorted from high to low. I want the cumlutavie% column to be the running sum of Sale%. right not it is calculating the running sum but in a different order. In the end I want to be able to filter products by the top % (i.e. top50% or top 80%)
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
90 | |
88 | |
83 | |
64 | |
49 |
User | Count |
---|---|
126 | |
110 | |
87 | |
70 | |
66 |