aggregated tables and weighted average

[Ffitzpatrick47]

The weighted average answers I've seen already starts from an aggregate table, I actually have to make an aggregate table first.  Here's an example, with out the machinery, so that one can be suggested...

https://drive.google.com/open?id=1o8vV9Ctv4gBgIfVTvJ9DG_oyDu27K9Mt

I have data that looks like this:

I calculate won = calculate(sum(won),won=1) and total is countrows(won) or whatever, the total is easy.  In actuality, the data is much more complex so I use the convoluted calculate function on won as a demo here.

I can calculate win rates with divide(won,total)

But now I want to calculate the weighted difference off this table

How would I do that?  Weight difference (which you can see in the spreadsheet) is just win rate minus average win rate weighted by the total.  I suspect a sumx function off a fictitious grouped table must be made, but there are so many moving parts, who know what that equation could be?

[Greg_Deckler]

You temp table that you speculate about would be done with an ADDCOLUMNS that adds your measures to a SUMMARIZE, summarized by Person and Product. May not need the ADDCOLUMNS and be able to do it all with SUMMARIZE. CALCULATETABLE also works well in these situations if you need to override some filters. 

[Ffitzpatrick47]

Do you know if there's a templated syntax or tutorial somewhere?  
This is like writing a complicated sql all in the space of one cell

[Greg_Deckler · Accepted solution]

First, can't open your Google drive file, 404. It's going to be something along the lines of:

Measure = 
VAR __myPerson = MAX('Table'[Person])
VAR __tmpTable = SUMMARIZE(ALL('Table'),[Person],[product],"__won",[won],"__total",COUNT([win]))
/*
this should return a table summarized by person and product that includes your "won" measure calculation as well as the total count of wins/losses.
*/
VAR __tmpTable1 = ADDCOLUMNS(__tmpTable,"__ratio",[__won]/[__total])
/*
This adds a column for win ratio
*/
VAR __average_ratio = AVERAGEX(__tmpTable,[__ratio])
RETURN
MAXX(FILTER(__tmpTable1,[Person]=__myPerson),[__ratio]) / __average_ratio

Something along those lines, beware of syntax errors I typed that without data.

[Ffitzpatrick47]

Hope this works in excel 2016.  This is one of those things... There's the dax tool from sqlbi, but otherwise it's nearly impossible to debug, especially if you have to nest all these variables because excel 2016 doesn't accept var