cancel
Showing results for
Did you mean:
Helper II

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

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?

1 ACCEPTED SOLUTION
Super User

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.
*/
/*
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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
4 REPLIES 4
Super User

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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Helper II

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

Super User

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.
*/
/*
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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Helper II

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

Announcements

#### Power BI T-Shirt Design Challenge 2023

Submit your creative T-shirt design ideas starting March 7 through March 21, 2023.

#### Power BI March 2023 Update

Find out more about the March 2023 update.

#### March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors
Top Kudoed Authors