Weighted Average for Multiple Variables by One Other Variable

[agalbraith]

So I have multiple values in columns that all get weighted by one other value to get the weighted average for each. Easily accomplished in SPSS by weighting the data set. Sure I could run the weighted average dax on every single one but that's a bit of a waste of time as I need to do this on multiple tables of data. I need to get all of these values into one bar chart as my end result in the dashboard, which is most easily accomplished by pivoting them into one attribute and one value column. How would I weight all these values by pivoting them though? I tried pivoting just the values and then weight by the other column I need to, but of course the results come out wonky since the rows have all been duplicated with the pivot. Is there a way to accomplish this?

Below is some dummy data to show you what I mean. I need to average all these other columns by weighting by #value

#value	FT	PT	Education	Military
6	0.85	0.05	0.04	0.06
25	0.65	0.05	0.3	0
18	0.5	0.5	0	0
56	0.95	0.01	0.02	0.02

But I need to unpivot them so that they go in a bar chart. I unpivoted them like this, but this is what doesn't work because then the weighting is off. I also have an ID column. Is there a way to update the formula so that it's only using one each of the weight based on the ID column? That's my thought with what I know of BI dax, I just can't figure out what it should be. This is the dax I was using: DIVIDE (SUMX(Table, Table[#value] * Table[Values]), SUM(Table[#value]))

#value	Attribute	Values
6	FT	0.85
6	PT	0.05
6	Education	0.04
6	Military	0.06
25	FT	0.65
25	PT	0.05
25	Education	0.3
25	Military	0
18	FT	0.5
18	PT	0.5
18	Education	0
18	Military	0
56	FT	0.95
56	PT	0.01
56	Education	0.02
56	Military	0.02

[jdbuchanan71]

No, this measure works fine also.

Weighted Average 2 = 
DIVIDE(
    SUMX('Table', 'Table'[Values] * 'Table'[#value]),SUM('Table'[#value])
)

I just created the 3 to see the values at each step.

[agalbraith · Accepted solution]

I went back to my data set and re-did the formula and it still wasn't right with my slicers but I realized it was a bad connection in my web. I was able to fix it! This formula does work even with the long table. Thank you!

[jdbuchanan71]

My calculation was done on the unpivoted data and it returns the results I think you want.  Am I not understanding something?

I have attached my sample file for you to look at.

[agalbraith]

so I need 3 separate dax formulas to get to the answer I need essentially based on the file. 

[jdbuchanan71]

@agalbraith 

The measure you gave looks right to me for a weighted average calc.  I broke it into 3 just to look at the values.

Weighted Values = SUMX('Table', 'Table'[Values] * 'Table'[#value])

Total Weight = SUM ('Table'[#value])

Weighted Average = DIVIDE([Weighted Values],[Total Weight])

What are you expecting to see that is different from the output?

[agalbraith]

Yes those weighted averages at the end are right (just not percentages). I am looking to have one final weighted average for each category. The output bar chart would look like below. I just did the weighted averages in excel and created a chart. This is what I need to accomplish. The formula works for each one individually in the first table I posted but that's a lengthy process. It's not working to produce the correct % when I do it on the pivoted table since those #values are duplicated 4 times.