Search code examples
excelpowerbipowerpivotpowerquery

Charting average sales per weekday on data composed of hours

I'm using PowerBI desktop and I'm creating a chart to display average sales per weekday:

enter image description here

My data is in the format below:

(sampled in Excel to remove sensitive information, added colors to facilitate visualization)

sample_data

My problem is: since each day is broken in 24 rows (hours), my average is wrong by a factor of 24.

For example, if I select January-2019 in the slicer, which has five Tuesdays (weekday code: 2), I want to see on the bar number 2:

  • (sum of amount where weekday = 2) / 5

Instead, I'm calculating:

  • (sum of amount where weekday = 2) / (24 * 5)

I can think of some ways to get this right, but they involve custom columns or auxiliary tables. I'm sure there is a simpler answer using DAX and measures, but I'm still learning it.

How can I correctly calculate this?

Solution

  • Let's assume your table name is "Data". Create 3 DAX measures (not calculated columns):

    Measure 1:

    Total Amount = SUM(Data[Amount])

    Measure 2: 

    Number of Days = DISTINCTCOUNT(Data[Date])

    Measure 3:

    Average Amount per Day = DIVIDE( [Total Amount], [Number of Days])

    Drop the last measure into a chart, it should give you the expected result.