Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Runing Percentage in order of RankX

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 :

OrganisationIDTradePointIDTradeCHareaProductSum
08EE59BB-4AD5-11E8-946E4C6C3407-6E34-11E9-94A2BFOOD    152 170  
08EE59BB-4AD5-11E8-946E5E6082C2-6E34-11E9-94A2WFOOD    466 383  
08EE59BB-4AD5-11E8-946E5774D6F0-7099-11E9-94A2AFOOD    260 354  
08EE59BB-4AD5-11E8-946EFE864288-6E33-11E9-94A2В2ВFOOD    386 929  
08EE59BB-4AD5-11E8-946E4C6C3407-6E34-11E9-94A2BALCO    361 283  
08EE59BB-4AD5-11E8-946E5E6082C2-6E34-11E9-94A2WALCO    395 152  
08EE59BB-4AD5-11E8-946E5774D6F0-7099-11E9-94A2AALCO    465 936  
08EE59BB-4AD5-11E8-946EFE864288-6E33-11E9-94A2В2ВALCO    223 434  

 loolike_1.JPG

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.

 

 

 

1 ACCEPTED SOLUTION

Hi,

See if this works.  You may download my PBI file from here.

Untitled.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

10 REPLIES 10
Ashish_Mathur
Super User
Super User

Hi,

Based on the data that you have shared, please show the exact expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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 =>loolike_1.JPG

 

 

 

 

 

 

 

 

 

 

* 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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

@Ashish_Mathur  , hello.

 

TradePointId = TPName and i work with SQL-base not a Excel

Hi,

See if this works.  You may download my PBI file from here.

Untitled.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

@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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

@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.

Untitled.pngUntitled1.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-lili6-msft
Community Support
Community Support

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.