topic Re: Daily Results vs. Total Results Average based on specific Day of Week and Category in DAX Commands and Tips

Daily Results vs. Total Results Average based on specific Day of Week and Category

Chrisjab28 — Thu, 08 Dec 2022 18:45:05 GMT

Hello Experts -

I am trying unsuccessfully to calculate the variance between a daily customer count to the total average for a defined period (last 3 months) by day of week (i.e. Monday) and location (Site Name).

Below is an example of the data set and the desired outcome:

Dataset

Desired Outcome:

So far, I've tried a couple of formulas leveraging AVERAGEX and ALLEXCEPT unsuccessfully:

Avg Customers =

VAR AvgCustomers = AVERAGEX('CUSTOMER PERFORMANCE',[Customer Count])

RETURN

IF (HASONEVALUE('CUSTOMER PERFORMANCE'[Date]),

AvgCustomers,

sumx(VALUES('CUSTOMER PERFORMANCE'[Date]), AvgCustomers))

Daily Average Cutomer = CALCULATE([Avg Customers] , ALLEXCEPT('CUSTOMER PERFORMANCE','Calendar'[Date].[Date],'CUSTOMER PERFORMANCE'[Site]))

If anyone can help, that would be greatly appreciated.

Thanks

Re: Daily Results vs. Total Results Average based on specific Day of Week and Category

Greg_Deckler — Thu, 08 Dec 2022 19:01:55 GMT

Chrisjab28 First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals 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

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

Re: Daily Results vs. Total Results Average based on specific Day of Week and Category

Chrisjab28 — Thu, 08 Dec 2022 19:51:43 GMT

Greg_Deckler Thank you for your reply. Unfortuantely, you reference links do not reference the average. The main issue has to do with the Average of all the values and comparing them the the daily total.

Re: Daily Results vs. Total Results Average based on specific Day of Week and Category

Greg_Deckler — Thu, 08 Dec 2022 21:22:38 GMT

Chrisjab28 Right, so use AVERAGEX instead of SUMX