Forum Discussion
Monthly averages issue
Super UserNPH2020 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.
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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.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]))