Forum Discussion

Leewism's avatar
Leewism
Frequent Visitor
2 years ago

Average Distribution by weekday

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 w...
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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.

Leewism's avatar
Leewism
Frequent Visitor
2 years ago

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.
Source the calculate the daily distribution on
A diagram overview of my stables
Diagram view

The table to apply the calculated distribution on.
Table to apply the 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.