topic Re: Average Distribution by weekday in DAX Commands and Tips

Average Distribution by weekday

Leewism — Fri, 26 Jul 2024 11:57:38 GMT

Hi, I'm trying to create a measure with an average distribution % per weekday. I previously solved it with calculated columns but that's slow. So a measure should solve some of that. I have a table with actual data on a daily level, I also have a calendar table with a True/False column if the dates should be included in the average.

The period total average is calculated with these measures:

ACT DISTRIBUTION OFFERED:=SUM(fDistribution[OFFERED])

FC DISTRIBUTION OFFERED:=CALCULATE([ACT DISTRIBUTION OFFERED];FILTER(dDate;dDate[DistrWeeks]))

Next I need a numerator to divide the total average to get to a daily average percentage. The numerator should be based on a weekday (e.g. Mondays depending on filter context). How would I go about calculating the average of a weekday?

Re: Average Distribution by weekday

SachinNandanwar — Fri, 26 Jul 2024 13:24:33 GMT

Could you please provide some sample data ?

Re: Average Distribution by weekday

Anonymous — Mon, 29 Jul 2024 03:10:39 GMT

Hi Leewism

Thanks for the reply from SachinNandanwar .

You can use the NETWORKDAYS function to calculate the working days between dates. NETWORKDAYS function (DAX) - DAX | Microsoft Learn

I created a simple sample data, hope it can help you.

Sample data:

Slicer table

DataTable

no relationship between two tables

Create a measure as follows

Measure = VAR _min = MIN('Date'[Date]) VAR _max = MAX('Date'[Date]) VAR _workday = NETWORKDAYS(_min, _max) VAR _SUM = CALCULATE(SUM('DataTable'[value]), FILTER('DataTable', [Date] >= _min && [Date] <= _max)) RETURN DIVIDE(_SUM, _workday)

Output:

If the above result is not what you want, then as SachinNandanwar said, you need to provide some sample data and the expected results based on the sample data so that we can help you better. How to provide sample data in the Power BI Forum - Microsoft Fabric Community Or show them as screenshots or pbix. Please remove any sensitive data in advance. If uploading pbix files please do not log into your account.

Best Regards,
Yulia Xu

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

Re: Average Distribution by weekday

Leewism — Tue, 30 Jul 2024 06:06:13 GMT

Thanks for the ideas so far. I'm not looking for working days though. I'm looking to sum the weekdays. I want to know the average of all Mondays, Tuesdays etc for every day of the week.

I can't share my file, hopefully these screenshots will help.

This is my source table to calculate the daily distribution on.

A diagram overview of my stables

The table to apply the calculated distribution on.

So far these are my results in a pivot table.

Sum of Offered is an implicit measure for now just the check my results. ACT or FC VOL DAY only show data for mondays, I want it show the weekly volume multiplied by the distribution percentage to get to a daily volume.

ACT DISTRIBUTION OFFERED:=SUM(fDistribution[OFFERED])

ACT DISTRIBUTION OFFERED WEEK:=CALCULATE([ACT DISTRIBUTION OFFERED];ALL(dDate[Date]);VALUES(dDate[Year_Wk]))

ACT DISTRIBUTION:=DIVIDE([ACT DISTRIBUTION OFFERED DAY];[ACT DISTRIBUTION OFFERED WEEK])

ACT DISTRIBUTION OFFERED DAY:=SUMX(VALUES(dDate[Date]);[ACT DISTRIBUTION OFFERED])

FC DISTRIBUTION OFFERED:=CALCULATE([ACT DISTRIBUTION OFFERED];FILTER(ALLSELECTED(dDate);dDate[DistrWeeks]))

FC DISTRIBUTION OFFERED DAY:=CALCULATE([ACT DISTRIBUTION OFFERED];FILTER(ALLSELECTED(dDate);dDate[Day of Week]=max(dDate[Day of Week])&&dDate[DistrWeeks]))

FC DISTRIBUTION:=DIVIDE([FC DISTRIBUTION OFFERED DAY];[FC DISTRIBUTION OFFERED])

dDate[DistrWeeks] is a column where I limit the weeks in the average. Like the last 10 weeks.

ACT VOL WEEK:=CALCULATE(SUM(fExportWeek[Actual]);FILTER(fExportWeek;fExportWeek[PI]="Offered"))

ACT VOL DAY:=SUM(fExportWeek[Actual])*[ACT DISTRIBUTION]

FC VOL WEEK:=CALCULATE(SUM(fExportWeek[Forecast]);FILTER(fExportWeek;fExportWeek[PI]="Offered"))

FC VOL DAY:=SUM(fExportWeek[Forecast])*[FC DISTRIBUTION]

Hope it makes sense now. I'm not sure if I am taking the right approach.