cancel
Showing results for
Did you mean:
Highlighted
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 :

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

## Re: Runing Percentage in order of RankX

Hi,

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

10 REPLIES 10
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

## Re: Runing Percentage in order of RankX

Hi,

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

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

* 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

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

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

## Re: Runing Percentage in order of RankX

Hi,

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

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

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

Announcements

#### Community News & Announcements

Get your latest community news and announcements.

#### Power Platform Summit North America

Register by September 5 to save \$200

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

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 205 members 2,595 guests
Recent signins: