Forum Discussion

Ffitzpatrick47's avatar
Ffitzpatrick47
Icon for Helper II rankHelper II
8 years ago
Solved

aggregated 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...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    8 years ago

    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.