Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
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
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.
Screenshot #1
Screenshot #2
Screenshot #3
Try this formula
PERCENTILEX.INC(ALLSELECTED(Facts[ID]),SUMX(ALLSELECTED(Facts[Period]),[Sum Values]),[Percentile Definition])
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
93 | |
86 | |
80 | |
69 | |
68 |
User | Count |
---|---|
226 | |
129 | |
119 | |
83 | |
77 |