Weighted average table calculation

Question

Hello! Trying to build what I had assumed was a simple table that is proving much more difficult. My source table is project &amp; team data that looks something like this:&nbsp;Category&nbsp; &nbsp; &nbsp;Team 1&nbsp; &nbsp;Team 2&nbsp; &nbsp;Team 3&nbsp; &nbsp;Team 4A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0.5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0.6&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0.25A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1.0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0.3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0.5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0&nbsp; &nbsp; &nbsp;&nbsp;B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0.25&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0.75&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0.5&nbsp;There are several lines for each category of project - for example, there can be 20 lines of Cat A, 15 of Cat B, etc.In my dashboard I inserted a table that sums all of the numbers for each column based on Category, with team totals and row totals (inserted as a Calculated Column). So the dashboard table looks something like this, using the data from the example above:&nbsp;Category&nbsp; &nbsp; &nbsp;Team 1&nbsp; &nbsp;Team 2&nbsp; &nbsp;Team 3&nbsp; &nbsp;Team 4&nbsp; &nbsp; &nbsp;TotalA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1.5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0.3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1.1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0.25&nbsp; &nbsp; &nbsp; &nbsp;3.15B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0.25&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0.75&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0.5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1.5Total&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1.75&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1.05&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1.1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0.75&nbsp; &nbsp; &nbsp; &nbsp; 4.65&nbsp;I'm&nbsp; trying to build another table that calculates weighted averages for this data. So for example, I want the number in Category A and Team 1 to calculate this: ((Team 1 total) / (Total total)) * (Team 1 Category A) = ((1.75) / (4.65)) * (1.5). I want this calculation in each of the cells of the table (obviously excluding the Total row/column).&nbsp;I've tried the Quick Measure for weighted average but couldn't get it to calculate correctly, or have it broken down by the right category (which in this case is named Category). Can anyone help me understand either the way to format the table so I get this information or the DAX equations to use? Thanks!

vvelarde · Accepted Answer

sfink22&nbsp;&nbsp;Hi, try with this:&nbsp;Step 1: Unpivot Data&nbsp; &nbsp; Go to Query Editor Select the Category Column and Unpivot Other Columns -- Close &amp; ApplyStep 2: Create a measure:&nbsp;WeightedAverage = 
VAR _SumValues=SUM(Table1[Value])
VAR _TeamTotal=CALCULATE(SUM(Table1[Value]),ALL(Table1[Category]))
VAR _Total=CALCULATE(SUM(Table1[Value]),ALL(Table1))

RETURN
DIVIDE(_TeamTotal ,_Total) * _SumValuesStep3 : Use in a matrix visual&nbsp;&nbsp;&nbsp;&nbsp;Regards&nbsp;Victor

Forum Discussion

Weighted average table calculation

2 Replies

Related content

Calculating Weighted Average

Calculating Weighted Sales

Weighted Average DAX

calculating weighted average

weighted average calculation