Wrong answer ... going crazy ... must be simple?

[sps-reporter]

I've built a "fake" call center performance report and the answers are not coming out as expected. I am looking  find the total amount of time on the phone for the call center on 3 different shifts for each employee. I am doing this by computing the average time on the phone for each employee for each shift. Then computing the average talk time for each shift. Then I take the difference between the employee shift average and the shift average and multiply that by the number of each shift the employee work. This should tell me on average how much time employees spent on the phone compared to the average. Of course this is not a fair comparison because people worked different amounts of time. So, I normalize the average talk time difference by the ratio of the average employee work time to the employee's total work time. Thanks for making it this far.

So, I did this same calculation using Python Pandas which I am confident is giving me the right answers. The Power BI report is almost there and gives me the right answers for the measures but not the correct answer when I try to aggregate the normalized difference by employee. Plus, slicing by date everything is incorrect. Here is the PBIX File  I am at wits end on how to make this work. I'm fairly new to Power BI so I am probably missing something simple. Any advice would be greatly appreciated.
Thanks

[Greg_Deckler]

@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

[sps-reporter]

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-reporter]

Oh the new PBIX file

[sps-reporter]

@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_Deckler]

@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?

[Greg_Deckler]

@sps-reporter - I downloaded the PBIX and took a look. Honestly, the only way to troubleshoot this is to generate the same intermediate data in Python that you have in DAX and compare to see where the numbers are off. I created a page for this purpose. Once you zero in on where the numbers are off, then you will be much closer to an answer. it's hard to help otherwise because things look pretty straight-forward. I mean, you use CALCULATE in an number of places uselessly but other than that there is nothing super complex here. I did notice that sometimes your numbers are above and sometimes below the Python numbers. So that indicates that it is likely NOT "Average Hours Worked" or "Shift Average". Those those are identical for each individual they could not be the cause.

Without seeing the Python and it's numbers, it's pure folly to try to determine why the two sets of numbers do not match. Updated PBIX is attached below sig.

[sps-reporter]

@Greg_DecklerThanks Greg, I was able to duplicate the Power BI results in Python. I confirmed the Python results by doing "hand" calculations. The measure for Total Normalize difference is ignoring individual worker shift averages and just averaging all shifts for each person and comparing that to the average of all shifts for all people. Thank you for confirming my careless use of calculate. Being new to PBI I was not really sure of when and when not to use that.  I was thinking I needed to change the filter context to affect the averages I was looking for and that worked as every measure up to the one I really wanted Total Normalized Differences. That  particular calculation is the one that seems to have the "wrong" context or not the one I was expecting. Essentially I want the Total Normalized Difference be a result of the difference between worker shift averages and the average of that shift for all workers. Not the average of all shifts for each worker. In the attached image the numbers in green are correct. If you sum those for worker Black you get -651.51 then multiple that by Average Hours Worked by all workers and divided by  Total hours worked by Black 2105.01/1852 I get -740.51 which is the answer for Total Normalized Difference I get with Python and hand calc verification. If you sum the numbers in the red for Black I get -718.74 which is correct if you normalized difference of all worker averages (overall shifts) and all shift averages. Puzzling. For some reason there is a context transition from the measures  used in the Total Normalized Different measure. Thanks for the time you have spent on this. I really appreciate advice from an expert.

[sps-reporter]

@Greg_DecklerI think your original tip is exactly my problem. I'm just not sure how to apply it. Can I use a measure in summarize?

m_Total 2 =

VAR __table = SUMMARIZE('Table1',[Category1],"__value",[m_Single])

RETURN

IF(HASONEVALUE(Table1[Category1]),[m_Single],SUMX(__table,[__value]))

Every row in this screen shot is correct except the total row.

Not sure how to proceed with the tip you provided. Thanks

[Greg_Deckler · Accepted solution]

@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]

@Greg_DecklerThat was it!!! Thank you! Now I just have to figure out why it works so I don't paint myself into this corner again. My DAX experience is wobbly but this will help me advance.

[Greg_Deckler]

@sps-reporter Well, the Measure Aggregation and Measures Total problems are kind of related. In fact, the second came from the first as I recall. Basically what it sounds like you are running into boils down to context. So, to put this in measures totals land which is easier to explain, when you are in the total line of table visualization, you are in the context of ALL. So your measure gets evaluated in this context and probably more often than not the total line is "wrong" because it is not an aggregation at the line level. So, by using a measure that works at the line level and then using something like SUMMARIZE or GROUPBY to recreate that line level context within the measure you can then use an "X" aggregation function across that table to get the "correct" aggregation at the total level. Now, when it comes to measure aggregation, understand that you have this same kind of issue within a hierarchy, not just at a total line.