cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Nick_kz Regular Visitor
Regular Visitor

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

Accepted Solutions
Super User
Super User

Re: Runing Percentage in order of RankX

Hi,

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

Untitled.png

 

10 REPLIES 10
Community Support Team
Community Support Team

Re: Runing Percentage in order of RankX

hi, @Nick_kz 

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.
Super User
Super User

Re: Runing Percentage in order of RankX

Hi,

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

Nick_kz Regular Visitor
Regular Visitor

Re: Runing Percentage in order of RankX

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

Super User
Super User

Re: Runing Percentage in order of RankX

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.

Nick_kz Regular Visitor
Regular Visitor

Re: Runing Percentage in order of RankX

@Ashish_Mathur  , hello.

 

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

Super User
Super User

Re: Runing Percentage in order of RankX

Hi,

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

Untitled.png

 

Nick_kz Regular Visitor
Regular Visitor

Re: Runing Percentage in order of RankX

@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 =( 

 

 

Super User
Super User

Re: Runing Percentage in order of RankX

You are welcome.  I am not clear about your next question.

Nick_kz Regular Visitor
Regular Visitor

Re: Runing Percentage in order of RankX

@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

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 205 members 2,595 guests
Please welcome our newest community members: