cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
noircrk
Frequent Visitor

Compare Daily Total with Day of Week Average

Hello All, I'm struggling with showing a daily total next to a day of the week average.  I've seen similiar posts regarding totals vs averages but I either don't understand the solution or am unable to adapt it to my situation.  Any assistance is greatly appreciated.

 

Assuming a date range of 03/29/2023 - 04/06/2023.  I can get the average by day of week "True Assign by Day" and the daily total "True Assign", as shown in the first table.  However, in the second table I want to show the "True Assign by Day" average from the first table to the daily "True Assign" in the second table.  But when I add the measure to the second table is mirrors the "True Assign" value.

 

Ultimately I need to compare the daily total against the average of that day of week for the date range selected.  The Business case is thus, Is today's (4/6/2023) volume of 502 higher or lower than the average Thursdays of 497 in the range selected?

 

noircrk_0-1681215634465.png

 

Formulas:

True Assign = COUNTROWS( 'Assign Data' )
True Assign by Day = AVERAGEX(DISTINCT('DtKey'[DtKey]),CALCULATE(COUNTROWS('Assign Data')))
 

Thank you for you time and consideration!

2 REPLIES 2
amitchandak
Super User
Super User

@noircrk , have week year column in your date table, have measures like

 

True Assign by Day = calculate(AVERAGEX(Value('DtKey'[DtKey]),CALCULATE(COUNTROWS('Assign Data'))), allselected())

 

True Assign by Week = calculate(AVERAGEX(Value('DtKey'[Week Year]),CALCULATE(COUNTROWS('Assign Data'))), allselected())

Thank you, @amitchandak .

 

What is the [Week Year] output or the dax to create it.  I currently have a [Week Number]=

VALUE( YEAR( 'DtKey'[Week End] ) & FORMAT( WEEKNUM( 'DtKey'[Week End] ), "00" ) )
 
 

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors