Weighted Average

[WestWinter]

Date	Region	Country	Males
01/01/2021	America	US	100
01/01/2021	America	Canada	150
01/01/2021	America	Mexico	110
01/01/2021	Asia	China	160
01/01/2021	Asia	Japan	100

So above, America will have 360 males while Asia have 260

Date	Country	Metric	Value
01/01/2021	US	Risk of Cancer	0.05
01/01/2021	Mexico	Risk of Cancer	0.02
01/01/2021	Canada	Risk of Cancer	0.024
01/01/2021	China	Risk of Cancer	0.053
01/01/2021	Japan	Risk of Cancer	0.02

So I am trying to find the weighted average of the above Metric (which could have many forms since it is in a long format)

I just want to match country by country, where the metric is multipled by the no of males in the first table accordingly to the dates.

[CNENFRNL · Accepted solution]

Merge table in PQ

Or DAX measures,

Ratio = 
IF (
    ISINSCOPE ( 'CASES'[Country] ),
    CALCULATE (
        AVERAGE ( LOOKUP[Value] ),
        TREATAS (
            SUMMARIZE ( 'CASES', 'CASES'[Date], LOOKUP[Country] ),
            LOOKUP[Date],
            LOOKUP[Country]
        )
    )
)

Total Weighted = 
SUMX (
    DISTINCT ( 'CASES'[Country] ),
    CALCULATE ( MAX ( 'CASES'[Males] ) ) * [Ratio]
)

[v-luwang-msft · Accepted solution]

Hi @WestWinter ,

Try like below ,create two column:

Column = 
VAR lookupvalue1 =
    LOOKUPVALUE (
        'Table B'[Value],
        'Table B'[Date], TableA[Date],
        'Table B'[Country], TableA[Country],
        0
    )
RETURN
    lookupvalue1 * TableA[Males]

Weighted Average = 
SUMX (
        FILTER ( TableA, TableA[Region] = EARLIER ( TableA[Region] ) ),
        TableA[Column]
    )
        / CALCULATE (
            COUNT ( 'TableA'[Country] ),
            FILTER ( TableA, TableA[Region] = EARLIER ( TableA[Region] ) )
        )

Final get :

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Best Regards

Lucien

[CNENFRNL · Accepted solution]

Merge table in PQ

Or DAX measures,

Ratio = 
IF (
    ISINSCOPE ( 'CASES'[Country] ),
    CALCULATE (
        AVERAGE ( LOOKUP[Value] ),
        TREATAS (
            SUMMARIZE ( 'CASES', 'CASES'[Date], LOOKUP[Country] ),
            LOOKUP[Date],
            LOOKUP[Country]
        )
    )
)

Total Weighted = 
SUMX (
    DISTINCT ( 'CASES'[Country] ),
    CALCULATE ( MAX ( 'CASES'[Males] ) ) * [Ratio]
)

[TomMartens]

Hey @WestWinter ,

you can use the DAX function LOOKUPVALUE (https://dax.guide/lookupvalue/) to pull the number of males to the second table by creating a calculated column in table 2.

Hopefully, this provides what you are looking for.

Regards,

Tom