Monthly averages issue

[NPH2020]

Hi all

I have a problem with monthly averages :

My underlying data is rolled up at a monthly level but one key parameter is the average daily volume.  This is calculated on a workday basis subject to bespoke holidays so I have this in a separate table.  I can easily define a a calcualted column to create the averages and this works to display the data correctly on a monthly basis but i need to be able to show the averages over quarters and years.

I have failed in linking the related number of workdays in the separate table while still calcuating the separate averages.  My aim is for this as a dynamic mneasure :

Sum of attribute / sum of period workdays (from related table)

Any guidance appreciated.

THanks !

[amitchandak]

@NPH2020 Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

if you have a common date table for both, you should be able to analyze these across month ,qtr and year.

[NPH2020]

Hi

Datasets as per below :

Table 1
EOM	attribute	Value
31-Jan	A	118
31-Jan	B	50
31-Jan	C	62
31-Jan	D	173
28-Feb	A	179
28-Feb	B	153
28-Feb	C	27
31-Mar	B	92
31-Mar	C	40
31-Mar	D	39
30-Apr	A	16
30-Apr	B	191
30-Apr	C	12
30-Apr	D	42
30-Apr	E	35
31-May	B	152
31-May	C	190
31-May	D	158
31-May	E	191
30-Jun	A	93
30-Jun	B	61
30-Jun	C	158
30-Jun	D	53
30-Jun	E	118

Table 2
EOM	Work day
31-Jan	18
28-Feb	19
31-Mar	22
30-Apr	18
31-May	16
30-Jun	22
31-Jul	20

Many thanks

[amitchandak]

@NPH2020 , Join to a common date table and there you can have a month, qtr and year.

You can create a measure like

Divide(Sum(Tabel1[Value]), sum(Table2[Work day]))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[NPH2020]

Hi

Sadly as the mock up of the data didn't account for the fact that the attrubtes repeat in the months and so it is summing the numerator correctly but then dividing but multiple sums of the workdays, rather than one.  I have tried the "distinct" function and it does not like that in sum functions, nor does it allow me to sum related columns.  It works as a calculated column, but then the aggregation doesn't.

sorry for the original dataset issue....

[amitchandak]

@NPH2020 , Try

Divide(Sum(Tabel1[Value]), sumX(Summarize(Table2, Table2[EOM],"_1" ,max(Table2[Work day])),[_1]))