Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello all,
I can not calculate the cumulative percentage in the order given by the ranking. This is necessary so that I can break outlets by 20% of the importance of sales. I need to divide these trade points into quality categories, those who bring the first 20% of the profit in sorting from Z to A, the second and so on to five. At the same time need work at filter the table by : 1. Organizations 2. Trade channel 3. areaProduct
I have table, simple example :
OrganisationID | TradePointID | TradeCH | areaProduct | Sum |
08EE59BB-4AD5-11E8-946E | 4C6C3407-6E34-11E9-94A2 | B | FOOD | 152 170 |
08EE59BB-4AD5-11E8-946E | 5E6082C2-6E34-11E9-94A2 | W | FOOD | 466 383 |
08EE59BB-4AD5-11E8-946E | 5774D6F0-7099-11E9-94A2 | A | FOOD | 260 354 |
08EE59BB-4AD5-11E8-946E | FE864288-6E33-11E9-94A2 | В2В | FOOD | 386 929 |
08EE59BB-4AD5-11E8-946E | 4C6C3407-6E34-11E9-94A2 | B | ALCO | 361 283 |
08EE59BB-4AD5-11E8-946E | 5E6082C2-6E34-11E9-94A2 | W | ALCO | 395 152 |
08EE59BB-4AD5-11E8-946E | 5774D6F0-7099-11E9-94A2 | A | ALCO | 465 936 |
08EE59BB-4AD5-11E8-946E | FE864288-6E33-11E9-94A2 | В2В | ALCO | 223 434 |
It looks like this
I have: Rank, Sales , ShareTradePoint , RuningShare and Simple percent categorization
RankX = IF([Sum_For20%] = BLANK() ; BLANK() ; RANKX(ALL('Fact_20%share'[TP_Name]);[Sum_For20%];;DESC;Dense))
Sum_For20% = IF(SUM('Fact_20%share'[Sum])=0;BLANK();SUM('Fact_20%share'[Sum]))
Share_Fro20% =
var SumInCat = CALCULATE(SUM('Fact_20%share'[Sum]);
FILTER(ALLSELECTED('Fact_20%share'[TP_Name]);'Fact_20%share'[TP_Name]<>BLANK()))
return
IF([Sum_For20%] = BLANK() ; BLANK(); [Sum_For20%]/SumInCat)
RuningShare = var SumPoint = CALCULATE(SUM('Fact_20%share'[Sum]); FILTER ( ALLSELECTED( 'Fact_20%share'); ISONORAFTER('Fact_20%share'[TP_Name];MAX('Fact_20%share'[TP_Name]);DESC))) var SumTotal = CALCULATE(SUM('Fact_20%share'[Sum]);FILTER(ALLSELECTED('Fact_20%share'[TP_Name]);'Fact_20%share'[TP_Name]<>BLANK())) return DIVIDE(SumPoint;SumTotal;0)
CatPoint = IF([RuningShare] <= 0,2 ; "A" ; IF([RuningShare] <= 0,4 ; "B" ; IF([RuningShare] <= 0,6 ; "C" ; IF([RuningShare] <= 0,8 ; "D" ;"E") )))
Pls help.
Solved! Go to Solution.
Hi,
See if this works. You may download my PBI file from here.
Hi,
Based on the data that you have shared, please show the exact expected result.
Hello, @Ashish_Mathur , @v-lili6-msft
I definitely need to build a ranking on the basis of sales, from larger to smaller. From 1 position to 3 should be the best customers who bring 20% of all profits, from 4 to 7 position the following customers who bring another 20% of all profits, etc.
It should look like this =>
* I created this table with the help function Group BY, if you know how to set the sorting at the table creation level in this table it is possible to maybe solve my question, as in the T-SQL Order by Sales ascending
Hi,
I think i can solve this. Share data (which i can paste in an MS Excel file). I am asking for this because the table that you shared in your first post does not have a TP_Name column whereas i see this column in images of your subsequent posts.
Hi,
See if this works. You may download my PBI file from here.
@Ashish_Mathur thank you so much !
The only better way to use in formulas is not ALL, but Allselected , so that you can use filters by other parameters such as the city / area of the product
And if we are talking, maybe we have an idea how to make a filter on the Category measure ?
There will be 5 categories in total: A B C D E , but the measure can not be used as a filter =(
You are welcome. I am not clear about your next question.
@Ashish_Mathur
The ultimate goal of all is the division into categories of the first 20%, the second 20%, and so on.
In an alphabetic expression, this will be A B C D E.
CatPoint = IF([RankX] =1 && [RuningShare] >0,2 ; "A"; IF([RankX] =1 && [RuningShare] >0,4 ; "A"; IF([RankX] =1 && [RuningShare] >0,6 ; "A"; IF([RankX] =2 && [RuningShare] >0,4 ; "B"; IF([RankX] =2 && [RuningShare] >0,6 ; "B"; IF([RankX] =3 && [RuningShare] >0,6 ; "C"; IF([RankX] =3 && [RuningShare] >0,8 ; "C"; IF([RankX] =4 && [RuningShare] >0,8 ; "D"; IF([RuningShare] <= 0,2 ; "A" ; IF([RuningShare] <= 0,4 ; "B" ; IF([RuningShare] <= 0,6 ; "C" ; IF([RuningShare] <= 0,8 ; "D" ;"E") )))))))))))
But I can’t select only category B or C in a separate filter, since measure can not be used as a filter
Hi,
You may download my PBI file from here. These are the buckets i created and the result i got.
hi, @Anonymous
First, your [Rankx] measure is based on [Sum_For20%], not based on [RunningShare] measure
RankX = IF([Sum_For20%] = BLANK() ; BLANK() ; RANKX(ALL('Fact_20%share'[TP_Name]);[Sum_For20%];;DESC;Dense))
So if you want to order by 1,2,4,3 not 1,2,3,4, just use this formula as below
RankX = IF([Sum_For20%] = BLANK() ; BLANK() ; RANKX(ALL('Fact_20%share'[TP_Name]);[RunningShare];;DESC;Dense))
Best Regards,
Lin
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |