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
zaza
Resolver III
Resolver III

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

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

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

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

@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"
    )

 

 

 

 

 

 

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.Capture.GIF

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.

 

123.JPG

 

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

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

Hi @Vandergledison 

 

Please find the pbix.

 

https://we.tl/t-erx0mbD03j

 

Regards,

Harsh Nathani

 

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

Thanks Harsh,

i still need to dig on the video and study all measures.

just a quick question, in the end i will have a Filter where i will select the prjects i want to visualize.

do i need to create a relationship with the Measured Table or isnt necessary? i noticed that if i dont have the relationshipt it wont filter. however, i thought that the MEasure Table calculate the values based on my main Data table, therefore that table has a relationship with the projcts Nr. table and i tought that would be filtered anyways.

thanks

Vandergledison

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

 

Harsh, you hit the nail in the head. 

now i will just need to dig in and undestand the measures you´ve wrote.

thanks again for the suport

VG

Hi @Vandergledison ,

 

Always a pleasure to help you. 

 

And in the process, I got some of my concepts cleared too and found some interesting things on power BI.

As an examplewhile declaring variables var __.80 will give you an error, while var __80 will not.

 

Thanks for your patience.

 

Regards,

Harsh Nathani

My Friend, i just noticed that if i select only some of the product in the filter, the ranking won't change.

the ranking of each product should change when the filter changes. if all are selected, it will work, but if only 5 projects for example are selected, the ranking are the same and keep the same whatever the filter shows...

any idea?

thanks

Hi,

Can you share what is the criteria for ranking.

In your earlier post you had mentioned that you want to rank 5 incase it falls between .80percentile and 60percentile.

Pls share me the entire business case so that I can help solve the problem.

Regards,
Harsh

Hi,

i would like to rank 5 (higher than 80p), 4 (higher than 60p), 3 (higher than 40p), 2 (higher than 20p) and 1 for the rest.

the idea is if i have 100 products i would have a certain ranking, by changing the filter to 50 products(for ex.) it should show the new ranking based only on this selected 50 projects.

i hope its more clear now.

the link with the file bellow

thanks a lot

Prioritization report 

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

Hi Harsh, 

what do you mean by "There is a function in DAX for %ile calculation for expressions."? i thought that we had to create a table becuase the DAx required columens...hmm im more confused now.

look, i exported the data from the first table visual with some project selected and calculated the percentilexc in Excel and it doesnt fit..

i will keep checking.

thanks a lot

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

 

Harsh, just a quick question:

in the 2 measures bellow, you did the first one in order to respond to the filter (projct No)?

im a bit confused here, just want to understand the mechanics.

thanks

 

Total Sales by Project No = CALCULATE([Total Sales],ALLEXCEPT(Sales,Sales[Project No.]))
 
 
twentypercentile =
                       CALCULATE(
                            PERCENTILEX.EXC(Sales,
                                [Total Sales by Project No],
                                .2),ALLSELECTED(Sales[Project No.]))

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