Measure to sum another aggregated measure's data

Question

I am working on a report that has data by month. I have created a measure that will calculate a cost per unit which divides the sum of dollars by the sum of production volume for the selected month(s):

Wtd Avg = SUM('GLData - Excel'[Amount])/SUM('GLData - Excel'[Production])

This works well and gives me the weighted average that I need per report category regardless of if I have one or multiple months selected. This actual and budget data is displayed below:

If you take time to total the actual costs you get $3.180. Where I am running into trouble is a measure to sum up to that total for a visual (This visual does not total sadly). Basically I need to sum the aggregated values that we see above. If I use the Wtd Avg measure I get the average for the total data set, or .53. I have attempted another measure, but am not coming up with the correct answer:

Total Per Unit Cost = sumX('GLData - Excel','GLData - Excel'[Wtd Avg])/DISTINCTCOUNT('GLData - Excel'[Date])

We see here I return $3.186. It is close, but it is not aggregating the right way to get exactly the $3.180:

My Total Per Unit Cost formula is off. Really I am simply interested in a measure to sum the post aggregated Wtd Avg measure we see in the first graph and total to $3.180 in this example.

Here is my data table:

Answer 1

As you probably know already, this is happening because measures are dynamic - if you are not grouping by a dimension, they will compute based on the overall table. What you want to do is to force a grouping on your categories, and then compute the sum of the measure for each category.

There are 2 ways to do this. One way is to create a new table in Power BI (Modeling tab -> New Table), and then use a SUMMARIZE() calculation similar to this one to define that table:

SUMMARIZE('GLData - Excel',[Category],[Month],[Actual/Budget],"Wtd Avg",[Wtd Avg])

Unfortunately I do not know your exact column names, so you will need to adjust this calculation to your context. Once your new table is created, you can use the values from that table to create your aggregate visual - in order to get the slicers to work, you may need to join this new table to your original table through the "Manage Relationships" option.

The second way to do this is via the same calculation, but without having to create a new table. This may be less of a hassle. Create a measure like this:

SUMX(SUMMARIZE('GLData - Excel',[Category],[Month],[Actual/Budget],"Wtd Avg",[Wtd Avg]),[Wtd Avg])

If this does not solve your issue, go ahead and show me a screenshot of your table and I may be able to help further.