topic Re: Weighted average of group minimums in DAX Commands and Tips

Weighted average of group minimums

abaligetig — Thu, 16 Jan 2020 21:21:44 GMT

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

Re: Weighted average of group minimums

JarroVGIT — Fri, 17 Jan 2020 19:04:27 GMT

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! 🙂

Re: Weighted average of group minimums

JarroVGIT — Fri, 17 Jan 2020 19:07:49 GMT

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:

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! 🙂

Re: Weighted average of group minimums

abaligetig — Sat, 18 Jan 2020 18:52:59 GMT

Thanks, that is exactly what I was looking for 🙂