cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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?

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
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())

Frequent Visitor

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" ) )

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors