topic Re: Percentile.inc aplied to all visible rows in measure column in DAX Commands and Tips

Percentile.inc aplied to all visible rows in measure column

Vandergledison — Sun, 26 Apr 2020 12:36:16 GMT

Hi guys,

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

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

Net Sales Data:

Project No.YearNet Sales

1	2020	2.63
1	2021	4.5
1	2022	5.27
1	2023	6.16
1	2024	6.77
1	2025	6.77
1	2026	6.77
1	2027	6.77
1	2028	6.77
1	2029	6.77
2	2020	1.49
2	2021	3.56
2	2022	6.14
2	2023	6.37
2	2024	6.62
2	2025	6.78
2	2026	6.95
2	2027	7.12
2	2028	7.3
3	2023	6
3	2024	10.8
3	2025	10.8
3	2026	10.8
3	2027	10.8
3	2028	10.8
3	2029	10.8
3	2030	10.8
3	2031	10.8
3	2032	10.8
4	2021	0.45
4	2022	1.35
4	2023	2.25
4	2024	2.32
4	2025	2.39
4	2026	2.46
4	2027	2.53
4	2028	2.61
4	2029	2.69
4	2030	2.77
5	2022	0.45
5	2023	1.12
5	2024	2.25
5	2025	2.32
5	2026	2.39
5	2027	2.46
5	2028	2.53
5	2029	2.61
5	2030	2.69
5	2031	2.77
6	2023	0
6	2024	0
6	2025	1
6	2026	1
6	2027	1
6	2028	1
6	2029	1
6	2030	1
6	2031	1
6	2032	1
7	2020	0.64
7	2021	1.35
7	2022	1.64
7	2023	2.06
7	2024	2.76
7	2025	2.89
7	2026	3.02
7	2027	3.16
7	2028	3.32
7	2029	3.48
8	2020	1.26
8	2021	2.7
8	2022	2.76
8	2023	2.81
8	2024	2.87
8	2025	2.93
8	2026	2.99
8	2027	3.05
8	2028	3.11
8	2029	3.17
9	2021	-2.4
9	2022	3.9
9	2023	6.35
9	2024	7.51
9	2025	10.22
9	2026	11.92
9	2027	15.22
9	2028	14.4
9	2029	13.61
9	2030	12.77
10	2020	6.58
10	2021	7.58
10	2022	7.1
10	2023	7.1
10	2024	7.1
10	2025	7.1
10	2026	7.1
10	2027	7.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 name	Net Sales	Percentile Net sales 0.8
1	A	59.18	6.77
2	B	52.33	7.018
3	C	103.2	10.8
4	D	21.82	2.626
5	E	21.59	2.626
6	F	8	1
7	G	24.32	3.192
8	H	27.65	3.062
9	I	93.5	13.768
10	J	56.76	7.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

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

Anonymous — Sun, 26 Apr 2020 14:18:44 GMT

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)

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

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

Vandergledison — Sun, 26 Apr 2020 14:38:00 GMT

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
1	A	59.18
2	B	52.33
3	C	103.2
4	D	21.82
5	E	21.59
6	F	8
7	G	24.32
8	H	27.65
9	I	93.5
10	J	56.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

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

Vandergledison — Sun, 26 Apr 2020 16:29:48 GMT

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.

A	B	C	D	E
Project No.	Product name	Net Sales	% of total net sales	80% Percentile from % of total net sales
1	A	59.18	13%	14%
2	B	52.33	11%	14%
3	C	103.2	22%	14%
4	D	21.82	5%	14%
5	E	21.59	5%	14%
6	F	8	2%	14%
7	G	24.32	5%	14%
8	H	27.65	6%	14%
9	I	93.5	20%	14%
10	J	56.76	12%	14%

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

Anonymous — Sun, 26 Apr 2020 17:13:52 GMT

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

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

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

Vandergledison — Sun, 26 Apr 2020 20:20:10 GMT

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.

thanks a lot

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

zaza — Sun, 26 Apr 2020 20:43:21 GMT

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 )

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

Vandergledison — Sun, 26 Apr 2020 20:49:12 GMT

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

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

zaza — Sun, 26 Apr 2020 22:21:49 GMT

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:

File here: .pbix

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

Anonymous — Mon, 27 Apr 2020 04:04:07 GMT

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.

https://we.tl/t-9WPKRZSWPi

Regards,

Harsh Nathani

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

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

Vandergledison — Mon, 27 Apr 2020 07:12:58 GMT

Thanks a lot for breaking down each DAX.

it worked 100%

just for me to understand the principles. Why do i have to create a Table and not single measures associated to the alredy given tables?

other thing, 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?. my biggest prob at the moment is that when i create a measure, sometimes it allows me to call other measures, and other time i find olny the columns that i already have availible.

Now, im trying to understand what you did and do the same for other measure.

im trying now to create a new measure using "if". i want to use the percentile table and give a rank to the Net sales %, like "if the %of net sales is higher than the 80% percentile, rank is 5". however, when i create a new measure or even continuing the DAX in your table, when if add "if", it opens olny measures that i can select and not the columns i need. i still dont get why doesnt show me all possible columns or measures to choose from.

thanks a lot

vandergledison

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

Vandergledison — Mon, 27 Apr 2020 07:00:56 GMT

Thanks a lot for your quick suport.

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

Anonymous — Mon, 27 Apr 2020 08:00:48 GMT

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

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

Vandergledison — Mon, 27 Apr 2020 08:12:54 GMT

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

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

zaza — Mon, 27 Apr 2020 08:51:43 GMT

Vandergledison

The reason we have to create a separate table is beacuse the PERCENTILE function only takes in as reference a column. So you need to have the exact column of values you want first stored somewhere. There might be a way to do this only in a measure but you would have to do it without using the PERCENTILE function.

To answer your second question, in your original table you have more multiple rows of net sales for each category. In the created tabel you only have one row for each and that is what we want.

To answer your third question is a more fundamental one and that is how measures and calculated columns work. You might want to watch some more in depth videos on this but in essence is this:

You can't say "IF this column is bigger than 50% then give me YES" What is column? how can a column be bigger than 50%? You need to Say IF the SUM or AVG ot this column is bigger than 50% then give me YES.

So essentially you need to wrap your columns into one of the aggregation functions MIN/MAX/SUM/AVG,etc.

measure = IF ( AVG ( 'Table[Column] ) > 0.5, "YES", "NO" )

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

Vandergledison — Mon, 27 Apr 2020 15:08:10 GMT

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

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

Vandergledison — Mon, 27 Apr 2020 20:40:03 GMT

Hi,

i noticed that the Percentile calculation doesnt change when i aply a filter to the Project Nr.

the 80% percentile should change acordingly to the %of net sales showed in the table, but they keep

constant, being calcualted from all the projects. it should change as the net sales and the %NEt sales are changing.

thanks.

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

Anonymous — Tue, 28 Apr 2020 05:21:40 GMT

Hi Vandergledison ,

I suggest your look at the below video for Percentiles.

https://www.youtube.com/watch?v=5AxtNdJ5wqk

Why does your value not change?

Below is an image which shows you the different percentiles for the %total. This works perfectly on a Card Visual. But, as soon as you drag in into the table you get a error. This is explained in the video above.

The current row context when dragged to the table is Project No. Now there is only one value of %total for A and hence any value below this or above this will not change and give you the same value.

Need to think in terms of row context and not cells in Power BI.

Regards,

Harsh Nathani

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

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

zaza — Tue, 28 Apr 2020 06:55:27 GMT

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.

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

Vandergledison — Tue, 28 Apr 2020 07:09:20 GMT

thanks Harsh,

would you be so kind to send me the pbix file? when i use the card to display the 80% percentile, im getting diff numbers...

thanks a lot