Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.