Forum Discussion
Ffitzpatrick47
Helper II
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 suggest...
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.
Greg_Deckler
Community Champion
Community ChampionFirst, 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.
Ffitzpatrick47Helper II
Helper IIHope 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