Regular Visitor

6 years ago

Solved

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

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

3 Replies

JarroVGIT

Resident Rockstar

6 years ago

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

abaligetig
Regular Visitor
6 years ago
Thanks, that is exactly what I was looking for 🙂

JarroVGIT

Resident Rockstar

6 years ago

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

Forum Discussion

Weighted average of group minimums

3 Replies

Related content

Weighted Average DAX

Calculating Weighted Average

Weighted Average Measure

Weighted average by month

Weighted Average