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

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

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

Highlighted
Helper III
Helper III

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

Thanks a lot for your quick suport.

 

Highlighted
Super User V
Super User V

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

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

Highlighted
Helper III
Helper III

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

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

 

Highlighted
Resolver II
Resolver II

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

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

 

 

 

 

Highlighted
Helper III
Helper III

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

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

 

Highlighted
Helper III
Helper III

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

 

 

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

Highlighted
Super User V
Super User V

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

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

Highlighted
Resolver II
Resolver II

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

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

Highlighted
Helper III
Helper III

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

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

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

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