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.

Regular Visitor

## Weighted average of group minimums

Hi Folks!

I have a following problem. Let say I have a tabular model with 2 tables:

• the first one has 3 columns: ReportingDate, Company, Price
• the second has 2 columns: Company, Weight

I would like to define a measure which calculates the weighted average of the companies price minimums (i have relation between the tables, however i don't know if it is needed or not).

I tried with this code

``````DEFINE
MEASURE 'Fact'[weighted_avg_of_minimums] =
SUMX (
VALUES ( 'Fact'[Company] ),
CALCULATE (
LOOKUPVALUE('Weights_to_Companies'[Weight], [Company], CURRENTGROUP() ) * MINA ( 'Fact'[Price] )
)
)
EVALUATE
(
SUMMARIZE (
FILTER (
'Fact',
'Fact'[ReportingDate] = DATE ( 2019, 2, 25 )
)
),
'Fact'[Company],
"Weighted minimums", CALCULATE ( 'Fact'[weighted_avg_of_minimums] )
)
)``````

of course the measure is not working with the CURRENCTGROUP() function. At this point i ran out of ideas..

I appreciate all your help 🙂

regards,

Gallusz

1 ACCEPTED SOLUTION
Resident Rockstar

I don't understand what you are saying completely, sorry. The outcome is a scalar value that is calculated over all companies together or is it caluclated per company? If it is the latter, I don't understand what you mean by 'weighted minimum'.  In your second table, every company is occuring once? And in the first column, there are many companies?

Based on a few assumptions, i started with these tables:

 Date Company Price 1/1/2020 A 100 1/2/2020 A 105 1/3/2020 A 110 1/4/2020 A 105 1/5/2020 A 100 1/6/2020 A 105 1/1/2020 A 90 1/2/2020 A 95 1/3/2020 A 100 1/4/2020 A 95 1/5/2020 A 90 1/6/2020 A 95 1/1/2020 B 40 1/2/2020 B 45 1/3/2020 B 50 1/4/2020 B 45 1/5/2020 B 40 1/6/2020 B 45 1/1/2020 B 30 1/2/2020 B 35 1/3/2020 B 40 1/4/2020 B 35 1/5/2020 B 30 1/6/2020 B 35
 Company Weight A 0.75 B 0.5

The measue is then this:

``````Measure =
VAR summaryTable = ADDCOLUMNS(SUMMARIZE('Table', 'Table'[Company], "Minimum", MIN('Table'[Price])), "Weight", LOOKUPVALUE(Weights[Weight], Weights[Company], [Company]))
RETURN
DIVIDE(SUMX(summaryTable, [Minimum] * [Weight]), COUNTROWS(summaryTable))``````

This gives me correct results both in total (unfiltered by date): ((90*0.75)+(30*0.5)) / 2 = 41.25, but also in a filted context like this:

Let me know if this is what you are looking for 🙂

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

Proud to be a Super User!

3 REPLIES 3
Resident Rockstar

I started of with these tables:

 Date Company Price 1/1/2020 A 100 1/2/2020 A 105 1/3/2020 A 110 1/4/2020 A 105 1/5/2020 A 100 1/6/2020 A 105 1/1/2020 A 90 1/2/2020 A 95 1/3/2020 A 100 1/4/2020 A 95 1/5/2020 A 90 1/6/2020 A 95 1/1/2020 B 40 1/2/2020 B 45 1/3/2020 B 50 1/4/2020 B 45 1/5/2020 B 40 1/6/2020 B 45 1/1/2020 B 30 1/2/2020 B 35 1/3/2020 B 40 1/4/2020 B 35 1/5/2020 B 30 1/6/2020 B 35

And this:

 Company Weight A 0.75 B 0.5

The measure I tested is this:

``````Measure =
VAR summaryTable = ADDCOLUMNS(SUMMARIZE('Table', 'Table'[Company], "Minimum", MIN('Table'[Price])), "Weight", LOOKUPVALUE(Weights[Weight], Weights[Company], [Company]))
RETURN
DIVIDE(SUMX(summaryTable, [Minimum] * [Weight]), COUNTROWS(summaryTable))``````

This results in this (I checked few values manually, it returns the weighted average of the minimum values). It also works as a scalar value over a longer period of time:

Let me know if this is what you are looking for 🙂

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

Proud to be a Super User!

Resident Rockstar

I don't understand what you are saying completely, sorry. The outcome is a scalar value that is calculated over all companies together or is it caluclated per company? If it is the latter, I don't understand what you mean by 'weighted minimum'.  In your second table, every company is occuring once? And in the first column, there are many companies?

Based on a few assumptions, i started with these tables:

 Date Company Price 1/1/2020 A 100 1/2/2020 A 105 1/3/2020 A 110 1/4/2020 A 105 1/5/2020 A 100 1/6/2020 A 105 1/1/2020 A 90 1/2/2020 A 95 1/3/2020 A 100 1/4/2020 A 95 1/5/2020 A 90 1/6/2020 A 95 1/1/2020 B 40 1/2/2020 B 45 1/3/2020 B 50 1/4/2020 B 45 1/5/2020 B 40 1/6/2020 B 45 1/1/2020 B 30 1/2/2020 B 35 1/3/2020 B 40 1/4/2020 B 35 1/5/2020 B 30 1/6/2020 B 35
 Company Weight A 0.75 B 0.5

The measue is then this:

``````Measure =
VAR summaryTable = ADDCOLUMNS(SUMMARIZE('Table', 'Table'[Company], "Minimum", MIN('Table'[Price])), "Weight", LOOKUPVALUE(Weights[Weight], Weights[Company], [Company]))
RETURN
DIVIDE(SUMX(summaryTable, [Minimum] * [Weight]), COUNTROWS(summaryTable))``````

This gives me correct results both in total (unfiltered by date): ((90*0.75)+(30*0.5)) / 2 = 41.25, but also in a filted context like this:

Let me know if this is what you are looking for 🙂

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

Proud to be a Super User!

Regular Visitor

Thanks, that is exactly what I was looking for 🙂

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