Forum Discussion
Helper IIaggregated tables and weighted average
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?
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_ratioSomething along those lines, beware of syntax errors I typed that without data.
4 Replies
- Greg_Deckler
Community Champion
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.
Ffitzpatrick47Do 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
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_ratioSomething along those lines, beware of syntax errors I typed that without data.