Filtering with Percentile.INC

[Anonymous]

I have two tables I am working with. One has multiple scores for multiple units (table 1) and the other is going to be a summary table with some other caclulations and will be unit specific (table 2). Like this

Table 1Table 2

I want to be able to find the 45th, 65th, and 85th, percentiles for each unit from Table 1 and put the value in Table 2. Is there a way to set up a calculated column to find the given percentile from the scores for a particular unit in Table 1 and store it in Table 2. For example if unit 401 had a 45th percentile of 70 I would want a value of 70 placed in B2 on table 2. (I know I show excel tables instead of power bi tables, but the visual is the same). 

I only have two units in the pictures but I will end up needing to do it for about 100 units each with around 100 samples, so I really don't want to have to make 3 measures for each unit.

Any help would be appreciated, thank you!

[Anonymous]

[Score 45] = PERCENTILE.INC ( T[Score], .45 )	
[Score 65] = PERCENTILE.INC ( T[Score], .65 )
[Score 85] = PERCENTILE.INC ( T[Score], .85 )

-- or

[Score 45] = PERCENTILE.EXC ( T[Score], .45 )	
[Score 65] = PERCENTILE.EXC ( T[Score], .65 )
[Score 85] = PERCENTILE.EXC( T[Score], .85 )

For the differences in the definition of the functions used, please see https://dax.guide/percentile-inc/ or https://dax.guide/percentile-exc/.

The above are MEASURES that will work with any filters that you put on the T table.

Best

Darek

[Anonymous]

I'm trying to avoid measures because I have 85 units each with 36 different scores I need to calculate 3 percentiles for. That would be close to 9,000 measures. Is there a way to do it with a column or variables so I can do it across a whole sheet of data and only look at one specific unit at a time?

[Anonymous]

Who says 9000 measures? You clearly don't understand how the measures I wrote work. You only need 3 measures for the whole setup to do what you want... Jesus... 😞

My advice is to learn something about DAX and Power BI.

Best

Darek

[Anonymous]

I'm trying to.... 

I understand you showed three measures and said that it would work for any filters. The problem I am trying to solve has 85 units and there are 36 contaminants per unit. I need to be able to filter through all the units individually and find the percentiles associated with each contaminent (score). I have 85 units each with 36 contaminants and I need 3 percentiles for each so 85*36*3 is just under 9,000. 

Maybe you could explain the measure a little more to me then and that could be what I am looking for?

[Anonymous]

You can put all your units on the row axis of a matrix, then all the contaminants on the column axis and then drop the measures onto the matrix. It'll display the percentiles for any combination of ( unit X contaminant ). 

What's the problem here? I can't see any... The matrix can be filtered for particular units and contaminants with slicers as well to limit the data to display.

Best

Darek

[Anonymous]

I need to still run calculations with those numbers. They are not my final values to put in a matrix and display with slicers. They are an intermediate step to get to the final value where I will put everything in a matrix to display.

[Anonymous]

What's the problem again? Once the measures work for ANY selections, you are free to use them in other calculations... I have already given you a piece of good advice: Please TRY TO LEARN SOMETHING ABOUT DAX AND POWER BI before you start using them for real work. There are many good books and websites about it. Just use Google to find them.

Your problems stem from the fact you know next to nothing of the tool that you're supposed to use. THAT'S PRETTY BAD. It's like telling somebody who knows (almost) no maths at all to use it to calculate something.

When I do something, I first try to learn the capabilities of the tool I'll be using. Is this not obvious? That should go without saying.

At this point I can tell you that you have all you need to do further calculations.

Best

Darek