Forum Discussion
Categorise Plus or Minus %
Hi Team
below is sample data of what i am calculating against
So far i am able to roll up the calculations whether by function or project and obtain a % difference between estimates/Actuals in $ and same for estimate/actuals in days. i do this with measures
what i am getting stuck with is categorising the differences across either function or project with a "Plus or minus %" category.
Am trying to mark rows that fall within the plus or minus groups of 10%, 20%, 30% etc
and then be able to count the times each row is counted plus the overall %, similar to output below
what i want to achieve can be done by summarizing the project and function columns into separate related tables and then create columns from calculations. in essence duplicating the same calculations within the new project or function tables.
am finding this a bit too long winded and hopefully learn how to do this simply in the original table through measures and count rows rather than through multiple tables and columns.
hopefully ive been able to explain this
Hi,
You can solve this with segmentation. I have solved a similar problem in the attached file.
Hope this helps.
13 Replies
Hi,
You can solve this with segmentation. I have solved a similar problem in the attached file.
Hope this helps.
Ashish_Mathur thankyou
i was hoping not to go along the lines of creating a custom table which was a path i was heading towards before i started this thread, but if its the only way around it, then happy to accept this as the solution
thankyou- You are welcome.
Batman_powerbi , Create a measure for categorizing the difference on the basis of your difference measure
Difference Category =
SWITCH(
TRUE(),
[Difference $] <= -0.3, "-30% or less",
[Difference $] <= -0.2, "-20% to -30%",
[Difference $] <= -0.1, "-10% to -20%",
[Difference $] <= 0.1, "-10% to 10%",
[Difference $] <= 0.2, "10% to 20%",
[Difference $] <= 0.3, "20% to 30%",
"30% or more"
)Then one more measure for count of category
Count of Categories =COUNTROWS(FILTER('Closed (TABLE)',[Difference Category] = EARLIER([Difference Category])))And then one for percentagePercentage of Categories =DIVIDE([Count of Categories],COUNTROWS('Closed (TABLE)'))bhanu_gautam
thankyou for the help
when i create the 2nd measure i obtain an error
"EARLIER/EARLIEST refers to an earlier row context which doesn't exist."
which i am not 100% what that means
any thoughts?Batman_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)
bhanu_gautam
Any further thoughts were i might be going wrong?Hi, Batman_powerbi
Thanks for bhanu_gautam positive reply. You can try following dax to achieve your need.
DAX:
Plus/Minus $ Range by Project Column = VAR __BASELINE_VALUE = CALCULATE ( SUM ( 'Table'[Estimate $] ), FILTER ( 'Table', 'Table'[Project] = EARLIER ( 'Table'[Project] ) ) ) VAR __VALUE_TO_COMPARE = CALCULATE ( SUM ( 'Table'[Actual $] ), FILTER ( 'Table', 'Table'[Project] = EARLIER ( 'Table'[Project] ) ) ) VAR _difference = IF ( NOT ISBLANK ( __VALUE_TO_COMPARE ), DIVIDE ( __VALUE_TO_COMPARE - __BASELINE_VALUE, __BASELINE_VALUE ) ) VAR _plusMinus = SWITCH ( HASONEVALUE ( 'Table'[Estimate $] ), _difference <= -0.3 && _difference > -0.4, "+/- 30%", _difference <= -0.2 && _difference > -0.3, "+/- 20%", _difference <= -0.1 && _difference > -0.2, "+/- 10%", _difference <= 0.05 && _difference > -0.1, "+/- 5%", _difference >= 0.05 && _difference < 0.1, "+/- 5%", _difference >= 0.1 && _difference < 0.2, "+/- 10%", _difference >= 0.2 && _difference < 0.3, "+/- 20%", _difference >= 0.3 && _difference < 0.4, "+/- 30%", "+/- 40% or more" ) RETURN _plusMinusCount = COUNTROWS('Table') % Overall = VAR _allCounts = CALCULATE(COUNTROWS('Table'),ALL('Table')) VAR _result = DIVIDE([Count],_allCounts) RETURN _resultBest Regards,
Yang
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
Anonymous
unfortunately the sample table and output is incorrect as it is evaluating every row in the tablepossibly i wasnt clear in my inital post
i wish to calculate and evaluate the Plus/Minus category against Project and Function separately
so in your sample, if i were to calculate this, there should be only a count of 3 total allocated to the %
if i were to do it by Role, i would have a count of 4 total due to the 4 separate roles.
