cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

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.

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

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

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors