cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ffitzpatrick47 Regular Visitor
Regular Visitor

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 suggested...

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

 

I have data that looks like this:

image.png

 

image.png

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)

image.png

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

image.png

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: aggregated tables and weighted average

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.

 

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


4 REPLIES 4
Super User
Super User

Re: aggregated tables and weighted average

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. 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Ffitzpatrick47 Regular Visitor
Regular Visitor

Re: aggregated tables and weighted average

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

Highlighted
Super User
Super User

Re: aggregated tables and weighted average

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.

 

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Ffitzpatrick47 Regular Visitor
Regular Visitor

Re: aggregated tables and weighted average

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