Forum Discussion
Categorise Plus or Minus %
Hi,
You can solve this with segmentation. I have solved a similar problem in the attached file.
Hope this helps.
Super UserBatman_powerbi , The issue is we have used measure instead of column
Create a caluclated column by going to modelling tab
Difference Category =
VAR diff = 'Closed (TABLE)'[Difference %]
RETURN
SWITCH(
TRUE(),
diff <= -0.3, "-30% or less",
diff <= -0.2, "-20% to -30%",
diff <= -0.1, "-10% to -20%",
diff <= 0.1, "-10% to 10%",
diff <= 0.2, "10% to 20%",
diff <= 0.3, "20% to 30%",
"30% or more"
)
Then create a measure
Count of Categories =
COUNTROWS(
FILTER(
'Closed (TABLE)',
'Closed (TABLE)'[Difference Category] = MAX('Closed (TABLE)'[Difference Category])
)
)
and last for percentage
DAX
Overall % =
VAR totalRows = COUNTROWS('Closed (TABLE)')
VAR categoryCount = [Count of Categories]
RETURN
DIVIDE(categoryCount, totalRows, 0)
thanks bhanu_gautam
unfortunately i get a duplication of the count of each time the Project is listed in the data table
the true nature of the output should be like below,
- bhanu_gautam
Update measure
Count of Categories =
CALCULATE(
COUNTROWS('Closed (TABLE)'),
ALLEXCEPT('Closed (TABLE)', 'Closed (TABLE)'[Difference Category])
)bhanu_gautam
sorry again that is still giving me a duplicate of counts
i believe its due to the initial calculated column instead of being a measure
- as a column the calculation then assigns a calculation to every row in the table, rather than a rolled up level by project/function