Cumulative Average of Average DAX

[2b3d510c]

Hi All, I'm looking for some advice on creating a measure that calculates a Cumulative Average Daily Call Length by day. My data is in a table structured as follows:

Call Date	Call Type	Total Calls	Total Call Length
1/1/2023	A	10	100
1/1/2023	B	10	100
1/2/2023	A	5	60
1/2/2023	B	10	120
1/3/2023	A	5	45
1/3/2023	B	5	45

My desired table would be:

Call Date	Total Calls	Total Call Length	Daily Average Call Length	Cumulative Average Daily Call Length
1/1/2023	20	200	10	10
1/2/2023	15	180	12	11
1/3/2023	10	90	9	10.33

The important thing to note here is the Cumulative Average Daily Call Length should be based on the Daily Average Call Length, not the average of individual calls (So the cumulative average for 1/2/2023 would be calculated as (10+12)/2 rather than (200+180)/(20+15). Can anyone provide advice on how to calculate this figure using DAX? 

[2b3d510c]

Sorry hopefully this is my last question, what was your formula for the 

[Daily Average Call Length]

the one I'm using doesn't appear to be working. I really appreciate all of the help!  

[DataNinja777]

Hi @2b3d510c 

Here you go. 

[DataNinja777]

Hi @2b3d510c 

I produced your required output as shown below.

I attach the link to the pbix file for this case.  

Average call length.pbix

[2b3d510c]

Hi @DataNinja777, 

I'm unfortunately unable to open the power bi you attached due to not having the latest version on my machine and cannot update due to device policy. 

Can you tell me the DAX you used for the Daily Average Call Length? I tried replicating the Cumulative Average Daily Call Length based on the screenshot, but it doesn't appear to work (I'm guessing because you have a different formula for Daily Average Call Length). 

Thanks! 

[DataNinja777]

Hi @2b3d510c 

My dax formulas are very simple like shown below:

This is the cumulative average daily call length measure:

Before that, I created a separate calendar table as a date dimension table and created a relationship with the fact table as shown in the data model view above.  

[2b3d510c]

@DataNinja777 , I got your formulas to work and the numbers look correct with my current dataset. 

However when I change the data around so the different call types have different average lengths for a particular day the formula no longer works (since you can't just use an average of call length, you have to take the # of calls into account for weighting). 

Call Date	Call Type	Total Calls	Total Call Length
1/1/2023	A	10	100
1/1/2023	B	5	200
1/2/2023	A	5	60
1/2/2023	B	10	120
1/3/2023	A	5	45
1/3/2023	B	5	45

[DataNinja777]

Hi @2b3d510c,

I found the weighted average dax formula in this forum, and used that in the attached pbix file.  

I prepared the visualization above using the older version of the Power BI desktop (August 2022 version) for compatibility purposes, and attached the link to the pbix file below.

Average call length (August 2022 version).pbix

Please check if this is what you require.    

[2b3d510c]

@DataNinja777 

Unfortunately those are not the averages I'm looking for. The latest formula you provided weights the cumulative average by the number of calls (so it correlates to the blue columns below). I'm looking for the averages in the green column where the cumulative Daily Average Call Length would be calculated as:

1/1 = 20/1

1/2 = (20+12)/2

1/3 = (20+12+9)/3

[DataNinja777]

Hi @2b3d510c 

Thanks for the clarification.  I produced your required output this time, but unfortunately, my Power BI file was upgraded automatically to September 2023 version, and I cannot share the file in the format you can open.  My formulas are very simple, broken down into components.  

Columns used in the calculated table above are as follows:

Summarize = SUMMARIZECOLUMNS('Calendar'[Date],"Average daily",[Daily Average Call Length])

Cumulative total = calculate(sum('Summarize'[Average daily]),'Summarize'[Date]<=earlier('Summarize'[Date]))

Day # = 'Summarize'[Date]-date(2023,01,01)+1

Weighted average = divide('Summarize'[Cumulative total],'Summarize'[Day #])