Forum Discussion
Wrong answer ... going crazy ... must be simple?
- 6 years ago
sps-reporter Yes, you can use a measure in SUMMARIZE. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149
The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.
sps-reporter I didn't download the PBIX yet but it sounds like a measures total problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
Thanks Greg, your reply is a solution to another issue that has been bugging me. This will come in handy. I've got more resolution on the problem I'm struggling with. Here are the measures in my PBIX:
///
/// Want all of these to be calculated on data sliced by date
///
// This should be the average Talk Time for each worker on each shift they worked
Worker Shift Average =
CALCULATE(
AVERAGE(work_performance[Talk Time]),
ALLEXCEPT(work_performance,work_performance[Date],work_performance[Name],work_performance[Shift])
)
// This is the count of each of the shifts they worked
Worker Shift Count =
CALCULATE(
COUNTA(work_performance[Shift]),
ALLEXCEPT(work_performance,work_performance[Date],work_performance[Shift],work_performance[Name])
)
// This is the average Talk Time of the shifts for all workers
Shift Average =
CALCULATE(
AVERAGE(work_performance[Talk Time]),
ALLEXCEPT(work_performance,work_performance[Date],work_performance[Shift])
)
// Total hours worked by each worker
Total Hours Worked =
CALCULATE(
SUM(work_performance[Shift Length]),
ALLEXCEPT(work_performance,work_performance[Date],work_performance[Name])
)
// Average of the total hours worked by each worker
Average Hours Worked =
CALCULATE(
DIVIDE(SUM(work_performance[Shift Length]),DISTINCTCOUNT(work_performance[Name])),
ALLEXCEPT(work_performance,work_performance[Date])
)
// Total Difference of worker shift average minus the overall shift average Talk time
// Total because this average difference is multiplied by the number of each of the shifts
// the worker worked
Total Difference =
CALCULATE(
[Worker Shift Count]*([Worker Shift Average]-[Shift Average])
)
// The total difference is normalized such that workers that worked less than the average
// time will have an increase total diffence and those that worked more than the average
// time will get a reduction in their total difference
Normalize Total Difference =
CALCULATE(
[Total Difference]*[Average Hours Worked]/[Total Hours Worked]
)
A table of these measures is not really of interest other than to spot check things. What I want is a plot (below) of the sorted normalized total difference over the workers to see how they compare, sort of a ranking. When I do this the normalized total difference measure ignores the shift-wise nature of this calculation and the total difference is simply the average Talk Time for each worker regardless of the shift and the average talk time of all the workers. This is a different calculation and not the one I'm interested in because each worker performs differently on each shift.
I'm sure this is a simple solution I am just at a stand still. I updated the PBIX file
Thanks
- sps-reporter6 years ago
Helper II
Oh the new PBIX file
- sps-reporter6 years ago
Helper II
Greg_Decklerthanks for the tip. I looked though the post you provided and downloaded the PBIX example file. Unless I missed something your tip has more to do with how measures are summarized in tables and matrices? I am pretty sure my problem is with context. Your post may be too advanced for me to associate what you wrote and demonstrated and how that relates to context.
Thanks for your advice. I would enjoy any other thoughts you have to offer.
- Greg_Deckler6 years ago
Community Champion
sps-reporter It was just a guess based on your description. You said the measures were correct but when you "try to aggregate the normalized difference" you get wrong answers. So that sounded like measure aggregation. Sorry for not replying, unless you @ me in replies I'll lose the thread. I get hundreds of notifications every day in my inbox on this community site. The vast majority are garbage notifications of another super user or someone replying to this or that thread or accepted solutions or whatever so it's easy to miss the original poster responding.
I have the PBIX downloaded. Just curious, why are you certain that Python is providing the right answer?