Summarize Data by Day of Week

[ptmuldoon]

I think I probably have all of the information (except maybe a measure), but I'm struggling how to figure out to summarize/compute data based on the day of week.

I am hoping to create a matrix grid showing Monday, Tuesday, Wednesday, etc, and then show totals for those particular days.

And base on a date/filter selected, that will compute all of the Mondays, Tuesdays, etc in that date range.

I already have calender table with Day of Week, and Day Number ( 1 - 7), and relationship to the data.  Im just not sure if I actually need to create a measure to do this or not?

[Anonymous · Accepted solution]

Hi  @ptmuldoon ,

Here are the steps you can follow：

1. Create calculated column.

Weekday =
FORMAT('calendar'[Date],"dddd")

2. Create measure.

Measure =
COUNTX(
    FILTER(ALLSELECTED('data_table'),
    FORMAT('data_table'[Date],"dddd")= MAX('calendar'[Weekday])),[Date])

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[ptmuldoon]

Thanks guys.  This is looking more of an issue with my data model, In rebuilding it from scratch to a new source (json files), I'm seeing now that something is off and different numbers than what I should be getting.

I'll have to trouble shoot that first before circiling back to getting some totals by day.  

[ptmuldoon]

Yes, I was also thinking I would need an explicit measure, I'm just struggling on what that should be.   I have a relationship on the date fields between my calendar table and data table.  And the calendar table includes they day of week. I just can't seem to work out the correct explicit measure to do the calc.

I'm thinking its something along with Calculate and groupby, but havent' figured it out yet.

[audreygerred]

You would just do a measure for the sum of whatever value you want the total on. So, if you have a sales table with a date and a value of sales, do Total Sales = SUM ('TableName'[Sales]), then in your viusal, put the days of week name in (for your date table) and your measure. It will put the appropriate total in the appropriate day of the week.

[audreygerred]

You can use implicit measures in Power BI, but ideally, you would create an explicit measure for your total and then in your visual put your day of the week and you will have the data you need. Here is a blog I wrote about implicit vs explicit measures: http://powerbiwithme.com/2023/10/25/the-explicit-implicit-measure-edition/