DAX troubleshoot, Percentile for grouped rows

[Joerobert]

Hello community,

See below screenshot has a reference. I have a table containing x number of rows and contains numerical whole numbers in the columns. I am utilizing the below DAX expression to calculate the summation of one of the column while filtering down other columns in the same table.

CalcCol(n) = CALCULATE(

    SUM(

          Table1[Col1]

     ),

    Table1[Col2]="x",

    VALUE(Table1[Col3(Year)])>2016,

    VALUE(Table1[Col(Year)])<2018)

)

By doing this, I am trying to create indivdiual groups baesd off the filters I am putting in the calculate function where each group has multiple rows. Once I have the total value for each calculated column, I am then creating a measure to calculate the percentile for each calculated column however the problem I am seeing is that, the calculation is taking into account each individual row value present in the grouping and this is skewing the final value.

I don't think i am understanding the evaluation context here or how to achieve the results I am looking for in DAX. I would like to calculate the percentile of the total sum value from my calculated columns.

Any help is appreciated.

[v-shex-msft]

Hi @Joerobert,

Since power bi data model not contains row/column index, I don't think you can simply calculate through column and rows.

I would like to recommend you use unpivot columns feature to transfer your columns to attr/value and replace column names to numeric.
After these steps , you can simply use if condition to control calculate which columns.

If above not help, please share some sample data to test.

Regards,

XIaoxin Sheng

[Joerobert]

Thank you for the feedback. I was not able to replicate your suggestion below, but think i found an alternative solution. See below screenshots as a reference. Below is a step by step summary of my approach for calculating the percentile for an aggregate total of rows.

1. I first began by applying the sumx function to the phase time column in Table 1 and then applying two separate filters to the dataset. You can see below in screenshot #1 that I was able to successfully achieve the correct result and then display the summation of times for each well when I changing the granularity in the table.
2. However the problem I was seeing was that I attempted to display the median value in the table and the 25th percentile value in a smart card, the calculation was being applied to the original row context.
3. In attempt to fix this calculation error with the percentile, I adjusted the DAX expression to include the ALLEXCEPT() function so that we can remove the original filter context and keeping a filter on the [Well Name] column; Reference screenshot #2 as example. This did not look good at first because when I brought this in to the same visualization, the aggregate value was being carried for each individual phase and was showing incorrect totals when I changed the granularity again to only display well name.
4. However, I was able to display what appeared to be the correct median value for the column total and the 25th percentile value.
5. In attempt to correct both issues, I ended up doing the following two things: a) include a logical IF statement to my previous DAX expression (step #3) and b) created a new table using the calculatetable() and selectcolumns() functions to carrying over the calculated columns
6. Once this new table was created, I was able to display the correct aggregate value in my summary table as well as when I changed the granularity to display on the well name.
7. I was also able to calculate the correct median value for the column total and able to display the correct 2th percentile value in the smart card

Screenshot #1

Screenshot #2

Screenshot #3

[Anonymous]

Try this formula

PERCENTILEX.INC(ALLSELECTED(Facts[ID]),SUMX(ALLSELECTED(Facts[Period]),[Sum Values]),[Percentile Definition])