cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Percentile.inc aplied to all visible rows in measure column

Hi guys,

i have a lookup table bellow related to the Net sales data table:

 

Project No.Product name
1A
2B
3C
4D
5E
6F
7G
8H
9I
10J

Net Sales Data:

Project No.YearNet Sales

12020       2.63  
120214.5
120225.27
120236.16
120246.77
120256.77
120266.77
120276.77
120286.77
120296.77
220201.49
220213.56
220226.14
220236.37
220246.62
220256.78
220266.95
220277.12
220287.3
320236
3202410.8
3202510.8
3202610.8
3202710.8
3202810.8
3202910.8
3203010.8
3203110.8
3203210.8
420210.45
420221.35
420232.25
420242.32
420252.39
420262.46
420272.53
420282.61
420292.69
420302.77
520220.45
520231.12
520242.25
520252.32
520262.39
520272.46
520282.53
520292.61
520302.69
520312.77
620230
620240
620251
620261
620271
620281
620291
620301
620311
620321
720200.64
720211.35
720221.64
720232.06
720242.76
720252.89
720263.02
720273.16
720283.32
720293.48
820201.26
820212.7
820222.76
820232.81
820242.87
820252.93
820262.99
820273.05
820283.11
820293.17
92021-2.4
920223.9
920236.35
920247.51
9202510.22
9202611.92
9202715.22
9202814.4
9202913.61
9203012.77
1020206.58
1020217.58
1020227.1
1020237.1
1020247.1
1020257.1
1020267.1
1020277.1

 

i would like to build a table like bellow. The Net Sales column shows the Total Sales of Product A regarding all its years of sales (long data above).

i used the measure bellow to get the 0.8 percentile:

Percentile Net sales 0.8 = PERCENTILE.INC('data for power BI porcentile'[Net Sales],0.8)
 
Project No.Product nameNet SalesPercentile Net sales 0.8
1A59.186.77
2B52.337.018
3C103.210.8
4D21.822.626
5E21.592.626
6F81
7G24.323.192
8H27.653.062
9I93.513.768
10J56.767.1

The problem is that i would like to have the percentile of the visible rows of this Table based on the total displayed in the Net sales column. Any ideas? thanks a lot

Excel data 

Pbix file 

6 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Resolver II
Resolver II

Re: Percentile.inc aplied to all visible rows in measure column

Erm... spam filter marked my message as spam, trying it again, hope this works!

 

Create a calculated TABLE:

 

Table = 
    SUMMARIZE ( 
        'data for power BI porcentile',
        'data for power BI porcentile'[Project No.],
        "Net Sales", SUM ( 'data for power BI porcentile'[Net Sales] )
    )

 

 

Then add a calculated column to it:

 

Net Sales % = 
    DIVIDE ( 
        'Table'[Net Sales], 
        CALCULATE ( 
            SUM ( 'Table'[Net Sales] ), 
            ALL ( 'Table' ) 
        ), 
        0 
    )

 

 

Finally create the measure:

 

80th Percentile = 
    IF ( 
        ISBLANK ( MAX( 'Project Nr'[Project No.] ) ), 
        BLANK(),
        CALCULATE (
            PERCENTILE.INC ( 'Table'[Net Sales %], 0.8 ),
            ALL ( 'Table' )
        )
    )

 

 

Connect it in the relationship view to get the Product name and voila:

Screen Shot 2020-04-26 at 11.51.13 PM.png

File here: .pbix

View solution in original post

Highlighted
Super User V
Super User V

Re: Percentile.inc aplied to all visible rows in measure column

Hi @Vandergledison ,

 

Sorry that i missed explain NetSales1 =

Net Sales1 = SUM('data for power BI porcentile'[Net Sales])
 
What i have simply done is added a table and calculate the sum of all the products and then calculated a percentile over NetSales.
 
Please find the pbix attached.
 
 
 
Regards,
Harsh Nathani
 
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!

View solution in original post

Highlighted
Super User V
Super User V

Re: Percentile.inc aplied to all visible rows in measure column

Hi @Vandergledison ,

 

Please find the pbix attached.

 

https://we.tl/t-gP09fMN6WE

 

 

This helps in filtering and ranking your products.

 

 

Regards,

Harsh Nathani

 

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!

 

View solution in original post

Highlighted
Super User V
Super User V

Re: Percentile.inc aplied to all visible rows in measure column

Hi @Vandergledison ,

 

Here you go. Hope this is what your requirement is.

 

Please find the pbix file attached.

https://we.tl/t-SZhdC34XRf

 

 

123.JPG

 

 

 

You do not need the newly calculated table. There is a function in DAX for %ile calculation for expressions.

 

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!

View solution in original post

Highlighted
Super User V
Super User V

Re: Percentile.inc aplied to all visible rows in measure column

Dear @Vandergledison ,

 

The idea is not to confuse you. We are here to help you.

 

Your requirement was 

1. Calculate the %ile based on Total Sales (or Sale%) for all projects.

2. Based on the %ile Calculated you wanted to Rank them i.e if the %ile is you wanted rank 5 (higher than 80p), 4 (higher than 60p), 3 (higher than 40p), 2 (higher than 20p) and 1 for the rest.

3. You also wanted the formula to Calculate the new %iles dynamically i.e if 50 projects are selected it should only Calculate the %iles of those 50 products and then Rank them based on the same criteria.

 

I hope my understanding is correct as your requirement has changed everytime we provided a Solution.

 

 

1. Confusion on PERCENTILE.EXC and PERCENTILEX.EXC

 

We were earlier calculating %iles based on a Summary Table having a row for Percentile.EXC. This same can be calculated using PercentilEX.EX where you can pass a measure instead of a Column. Please see what the formula does.

 

 

PERCENTILE.EXC

Returns the k-th (exclusive) percentile of values in a column.

Syntax

PERCENTILE.EXC ( <Column>, <K> )
PARAMETER ATTRIBUTES DESCRIPTION
Column 

A column containing the values.

K 

Desired percentile value in the interval [1/(n+1),1-1/(n+1)], where n is a number of valid data points.

Return values SCALAR A single variant value.

 

PERCENTILEX.EXC

Syntax

PERCENTILEX.EXC ( <Table>, <Expression>, <K> )
PARAMETER ATTRIBUTES DESCRIPTION
Table
ITERATOR
 

Table over which the Expression will be evaluated.

Expression
ROW CONTEXT
 

Expression to evaluate for each row of the table.

K 

Desired percentile value in the interval [1/(n+1),1-1/(n+1)], where n is a number of valid data points.

Return values SCALAR A single variant value.

 

So both the functions does the same thing, only difference is the parameters we pass into it. I hope I am able to clear your doubts for this.
 
 
2. Excel Does not show the same value
The %iles which were shown in the card above were based on the filtered project no.
Let me explain this in the description below.
 
Below is attached image which shows the %iles based on all of your project sales. i.e Column Total Sales.  Since there are lots of projects whose Sales Value is Zero, most of your %iles fall in the Zero Category.
1.JPG
 
 
 
Now below is the dynamically calculatd new %iles based on the filterd project nos i.e Total 7 column which I have filtered and it gives me the new ranks based on your criteria.
 
2.JPG
 
 
 
I hope I have been able to clear your doubts.
 
Please find final pbix attached.
 
 
 
Regards,
Harsh Nathani
 
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!
 

 

View solution in original post

Highlighted
Super User V
Super User V

Re: Percentile.inc aplied to all visible rows in measure column

Hi @Vandergledison ,

 

Please find 2 files attached.

 

v4 is using related and as per your grouping.

https://we.tl/t-hHaqM7OiLN

 

v2 is creating clusters based on Total Sales.

 

https://we.tl/t-MMXNCYyM7M

 

Hope it solves the issue.

 

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!

View solution in original post

37 REPLIES 37
Highlighted
Super User V
Super User V

Re: Percentile.inc aplied to all visible rows in measure column

Hi @Vandergledison ,

 

Do you mean you want to show the percentile of the netsales? 

If this is the case, all your products will have percentile equal to the net sales, as there is only one entry for each of the net sales of the product and the row context is of the product.

 

Incase you want a card to show you .8 percentile of the netsales the value comes out to be 86.64.

 

You can create a Summary Table:

 

Table = SUMMARIZE('data for power BI porcentile','Project Nr'[Product name],"Sales",[Sum of Product Name])
 
Q0.8 = PERCENTILE.EXC('Table'[Sales],0.8)
 
123.JPG
 
 
Regards,
Harsh Nathani
 
 

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!

In case it did not help, please provide additional information and mark me with @ so that I can investigate further

 

Highlighted
Helper III
Helper III

Re: Percentile.inc aplied to all visible rows in measure column

Hi Harsh, the sales i have in the table are the total Sales for each product. This values came from another table containing the sales per year. what i need now is a new measure that provides me the percentile 80% of each product but now based on the total net sales as you see bellow

 

Project No.Product name Total Net Sales
1A59.18
2B52.33
3C103.2
4D21.82
5E21.59
6F8
7G24.32
8H27.65
9I93.5
10J56.76

The values for each product will be the same. What i want to do in the end is to Rank each product. I want to have in each row an if function that will score each product regarding its location within a certain percerntile. for example if product A has total sales higher than the percentile 80% it should get a 5, if its higher than 60% and lower than 80%, it should get a 4, and so on..

i hope its more clear.

thanks

Highlighted
Helper III
Helper III

Re: Percentile.inc aplied to all visible rows in measure column

basically i would like to do like in EXCEL.

in excell i applied the formula: =PERCENTILE.INC($D$2:$D$11,0.8)

in column E is showing the result. i just want the same in power BI via  some measure.

ABCDE
Project No.Product nameNet Sales% of total net sales80% Percentile from % of total net sales
1A59.1813%14%
2B52.3311%14%
3C103.222%14%
4D21.825%14%
5E21.595%14%
6F82%14%
7G24.325%14%
8H27.656%14%
9I93.520%14%
10J56.7612%14%
Highlighted
Super User V
Super User V

Re: Percentile.inc aplied to all visible rows in measure column

Hi @Vandergledison ,

 

Create a table.

 

Table = ADDCOLUMNS(
ADDCOLUMNS(
SUMMARIZE('data for power BI porcentile','data for power BI porcentile'[Project No.],'Project Nr'[Product name]),
"ns",[Net Sales1],
"%tot",CALCULATE([Net Sales1],ALL('data for power BI porcentile'))),
"%%tot", [ns]/[%tot])

Create a Calculated Column in the table
 
Per = PERCENTILE.INC('Table'[%%tot],.8)
 
You will get a table created like
 
123.JPG
 
1234.JPG
 
Regards,
Harsh Nathani
 

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!

In case it did not help, please provide additional information and mark me with @ so that I can investigate further

Highlighted
Helper III
Helper III

Re: Percentile.inc aplied to all visible rows in measure column

Hi,

thanks a lot, im still cracking my head to understand what you did (sorry, very new to Power BI).

somehow, when i tried to select the [Net Sales] from the table 'data for power BI porcentile' it doesnt work, it only shows the availible measures.

by just copy pasting what you have sent me, it doesnt find the [Net Sales1] i tried to change to [Net Sales] and also didnt work.

Capture.GIF
thanks a lot
Highlighted
Resolver II
Resolver II

Re: Percentile.inc aplied to all visible rows in measure column

There might be a better way to do this, but this should work:

 

Measure = 
    VAR __totals = 
        CALCULATE ( 
            SUM ( 'data for power BI porcentile'[Net Sales] ),
            ALL ( 'Project Nr' )
        )

    RETURN
    
    DIVIDE ( SUM ( 'data for power BI porcentile'[Net Sales] ), __totals, 0 )

Screen Shot 2020-04-26 at 10.42.17 PM.png

Highlighted
Helper III
Helper III

Re: Percentile.inc aplied to all visible rows in measure column

hi,

The percentile value you have per product refers to the 5 or more years that are counted for each product. what i need is the percentile based on the total net sales as presented in the visual table.

thanks

Highlighted
Resolver II
Resolver II

Re: Percentile.inc aplied to all visible rows in measure column

Erm... spam filter marked my message as spam, trying it again, hope this works!

 

Create a calculated TABLE:

 

Table = 
    SUMMARIZE ( 
        'data for power BI porcentile',
        'data for power BI porcentile'[Project No.],
        "Net Sales", SUM ( 'data for power BI porcentile'[Net Sales] )
    )

 

 

Then add a calculated column to it:

 

Net Sales % = 
    DIVIDE ( 
        'Table'[Net Sales], 
        CALCULATE ( 
            SUM ( 'Table'[Net Sales] ), 
            ALL ( 'Table' ) 
        ), 
        0 
    )

 

 

Finally create the measure:

 

80th Percentile = 
    IF ( 
        ISBLANK ( MAX( 'Project Nr'[Project No.] ) ), 
        BLANK(),
        CALCULATE (
            PERCENTILE.INC ( 'Table'[Net Sales %], 0.8 ),
            ALL ( 'Table' )
        )
    )

 

 

Connect it in the relationship view to get the Product name and voila:

Screen Shot 2020-04-26 at 11.51.13 PM.png

File here: .pbix

View solution in original post

Highlighted
Super User V
Super User V

Re: Percentile.inc aplied to all visible rows in measure column

Hi @Vandergledison ,

 

Sorry that i missed explain NetSales1 =

Net Sales1 = SUM('data for power BI porcentile'[Net Sales])
 
What i have simply done is added a table and calculate the sum of all the products and then calculated a percentile over NetSales.
 
Please find the pbix attached.
 
 
 
Regards,
Harsh Nathani
 
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!

View solution in original post

Helpful resources

Announcements
Ignite

Microsoft Ignite

This will be a conference that you do not want to miss!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Don't miss the Power BI Dev Camp this week!

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors