Forum Discussion

NPH2020's avatar
NPH2020
Regular Visitor
6 years ago

Monthly averages issue

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 su...
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

NPH2020's avatar
NPH2020
Regular Visitor
6 years ago

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's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    NPH2020 , Try

     

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