Sum of averages using measures

[Anton93]

Assuming I have the following data: 

Where the green headers are the data I have and the red headers are the data I want to produce using a measure to plot as a line graph. 

The user has the option to use filters to select projects (1 and 2) and groups (1 and 2), so that the line of capacity varies accordingly. 

I already tried writing the following measure: 

SumAvg2 = SUMX(VALUES('Table'[Group]), AVERAGE('Table'[Avg Capacity]))

 But it computes the values as 16.5 whereas I am expecting the value of 33 when the user selects all the two projects and two groups in the filter.  

I am aware that I can instead drag the column Avg capacity to the y axis and set it as "Avg" to show the required view. But for other reasons I wont be able to pull that column in the visualization and would have to create a measure to produce the value. 

Any thoughts?

[DOLEARY85]

Hi, 

try these:

Sum of Avg Per Project = CALCULATE(sum('Table (2)'[Avg Capacity]),ALLEXCEPT('Table (2)','Table (2)'[Project Name]))/CALCULATE(DISTINCTCOUNT('Table (2)'[Date]),ALLEXCEPT('Table (2)','Table (2)'[Project Name]))

Total Sum of Avg Per Project = CALCULATE(sum('Table (2)'[Avg Capacity]),ALL('Table (2)'))/CALCULATE(DISTINCTCOUNT('Table (2)'[Date]),ALL('Table (2)'))

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

[Anton93]

Hi @DOLEARY85 , thanks for the help. 

As I mentioned in the other comment, this is what I am trying to achieve: 

1. When user selects Project 1, group 1, the line would have value of 10

2. When user selects project 1, group 1 and 2, the line would have value of 11

3. When user selects project 1 & 2, group 1 & 2, the line would have value of 33

I think the first code you provided is close to it as:

1. When I select Project 1, Group 1 it gives me value of 11 (instead of 10) 

2. When I select Project 1, Group 1 & 2 it gives me value of 11 (correct)

3. When I select Project 1 & 2, Group 1 & 2, it gives me value of 33 (correct) 

Essentially I think when I select a single project and a single group, it seems to be giving me the sum of averages of both the groups. 

Any thoughts?

[DOLEARY85 · Accepted solution]

you could try using ALLSELECTED in the expression:

Sum of Avg Per Project = CALCULATE(SUMX('Table (2)','Table (2)'[Avg Capacity]),ALLSELECTED('Table (2)'[Project Name],'Table (2)'[Group]))/CALCULATE(DISTINCTCOUNT('Table (2)'[Date]),ALLSELECTED('Table (2)'[Project Name],'Table (2)'[Group]))

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

[Anton93]

Hi @DOLEARY85 , 

The guidance you provided worked great and I was able to implement it in my dashboard. But during the course of testing I realised that it was effecting one other feature. 

My X Axis is a timeline, so the user has the option to drill down to the day view as well as drill up to see the monthly view. 

Looking at the day view, everything works great:

The purple line is exactly summing up values based on the filters selected. So the interpretation in this day view is that you have capacity (line) to do the work (bar). 

But the problem arises when user drills to the monthly view:

The bars sum up as expected on a monthly basis, but the purple capacity line still remains with the value of 11 whereas it should take the value of :

   value per day*number of working days in month= sum of total capacity

   Example of may: 11*23=253 which should be where the red line should possibly be. 

As far as I understood, the bars are getting changed as I have set it to "Sum" , but since I use a measure for the line, it always computes the values based on filter selection and is independent of the drill up and drill down of visual. 

Any thoughts on a possible solution?

[Anton93]

Hi @DOLEARY85 , 

I tried to envison the final data set better now by also adding on the different timeline perspective (which would be my x-axis)

I hope this can make it easier to understand what I am trying to achieve? 

[Anton93]

Hi! 

Unfortunately no, I just created that to give an overview and try explain it easily. 

For context, there would be 10 projects, but each project would have the same 8 groups constant. And each group would then have some capacity every day of the month from 2023 and beyond. 

[ray_aramburo]

With some creativity with DAX, I came up with something that might work for you: First let's get the average by projects and groups: Average Measure = CALCULATE(AVERAGEX(SUMMARIZE(Table, Table[Project], Table[Group]), CALCULATE(SUM(Table[AvgCapacity]))), ALLEXCEPT(Table, Table[Project])) Then, to get the sum of averages you can use: Sum of Averages = CALCULATE(SUMX(VALUES(Table[Project]), [Average Measure]), ALL(Table))

[Anton93]

Hi @ray_aramburo thanks for the hint. I tried implementing it but somehow didnt arrive at the solution I was aiming for. 

To give a bit more context, this is what I was hoping to achieve:

1. When user selects Project 1, group 1, the line would have value of 10

2. When user selects project 1, group 1 and 2, the line would have value of 11

3. When user selects project 1 & 2, group 1 & 2, the line would have value of 33

Currently it is always getting the value of 49.5 irrespective of any selection with the snippet I got from you. Here is a screenshot for reference: 

Any thoughts? 

[ray_aramburo]

As suggested by @DOLEARY85 try using ALLSELECTED instead of all the ALLEXCEPT from the code.

[Anton93]

Hi @ray_aramburo , 

I tried to envision the data much better by introudcing a new month as that is how it would be. Essentially the y axis would not be a single flat line by a stepped line based on the month and then the selection of project and group combinations. 

Hope this makes it more clear? 

[ray_aramburo]

Does the whole data set only contain 2 groups?