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

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

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

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

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

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

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

@Vandergledison 

 

The ALLEXCEPT Calculates the Total Sales grouping the Sales with project number.

Read AllEXCEPT, Remove all FILTER except on the column mentioned.

 

Since you are calculating percentiles for the sales with project no, this needs to be the measure over which %ile calculation will be done.

 

The Twenty% is used when you filter via project no. It will calculate the %iles of the selected rows only and not the entire Sales Column.

 

Regards,

Harsh Nathani

 

 

This is quite interesting and i spend the whole day changing the DAW and trying to learn what would be the variations by changing some fo the paramenters. The AllEXCEPT was difficult to understand but i think that i got it how it works.

My last step, is to create Clusters, or Groups to consolidate the project Nrs.

i thought that would be very easy, i added a new filter A (for the first 25 projects), B (next 25), and so on.

the problem now, is that due to that ALLEXCEPT, if i understood correctly, it will not consider previous filters, which makes the Rank on %ile not work on that table.

in the end, i will need to Select A, and have the (e.g) 25 projects which its respective %ranking as you provided.

i know that is more than abusing but any idea on how to work this around?

thanks a lot

modified file 

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

Hi Harsh,

The V4 worked perfectly. Im just trying to understand the mechanics and one thing i couldnt understand. You had to chreate a Column [Clusters] that relates to the [MKST] from the 'Masterdata'. why doesnt work to simply grab the [MKST] column and add in the visualization if both table are connected via a relationship.

in the future, the 'Masterdata' will be a DIM table containing several categories to act as filters. i thought that i could simly create different Filters in the futre with the categories in 'Masterdata' filtering the 'Sales' table.

rgds

VG

Thank you so much.

 

Dear Harsh,

im sorry if somehow i misscomunicate. The final request didnt change and your way of thinking was spot on!

on your last file, i missed the EX on the percentilEX.exc and i thought that was the same, so thats why i couldnt see the difference and obviously the values in excel werent the same.

thanks a lot for your extra patience!!!

I learned a lot

VG

i totally undestood. thanks.

look, just one thing before i can move full throttle:

you wrote this before the "Calculate" part:

ISBLANK ( MAX( 'Project Nr'[Project No.] ) ),
BLANK(),

why is this necessary?

thanks

 

@Vandergledison Try to remove the IF statement and only have the CALCULATE statement and see what happens visually in your table. Then you will understand why I'm using the IF ISBLANK statement.

 @Vandergledison ,

 

Thanks for accepting my solution.

 

 

1. i have already in my original data table the Net Sales for each product. if i simply add the column Net sales column on the side of each Product nr. i would have automaticly the Sum of this Net Sales column for each product. Why do i have to create a new one ("Net Sales") in the new table?

 

Creating a new table is required  as Percentile requires a column for Calculation. The formula for Percentile requires a column and a value. PERCENTILE.EXC ( <Column>, <K> )

 

Net Sales is a Column but the Sum of Net Sales internally calculates the percentile separately for A, B, C , D as row context is of product name.

A has 10 rows and it calculates the percentile for these 10 rows and that is the reason you were getting 6.77 for A when you calculated Percentile Net Sales 0.8.

 

This is the reason we are creating a separate table with a column net sales and then calculating the percentile over net sales %.

 

Hope this makes sense now.

 

 

Regards,

Harsh Nathani

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

ahhhh, now makes a lot of sense.. it still seems to me a workaround  and i just need to understand the mechanics so i can run my own.

look, now im trying to create an IF function. i manged to do it, however i had to create a new calculated column.

when i tried to create this column inside of the measured table as you did, somehow doesnt work. when i start typing "rank net sales"=if(....  - I cant choose the new columsn of my new table, 

i want simply to do a :

rank Net sales% = IF('New table'[Net Sales %]>=[80th Percentile],5,IF('New table'[Net Sales %]>=[60th Percentile],4,IF('New table'[Net Sales %]>=[40th Percentile],3,IF('New table'[Net Sales %]>=[20th Percentile],2,1))))

 

is it possible to do the same but not in a new calculated column but inside of the measure table you´ve created?

thanks

 

harshnathani
Community Champion
Community Champion

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

 

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

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%

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

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

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

Thanks a lot for your quick suport.

 

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.

Top Solution Authors