Forum Discussion

WestWinter's avatar
WestWinter
Helper II
5 years ago
Solved

Weighted Average

DateRegionCountryMales
01/01/2021AmericaUS100
01/01/2021AmericaCanada150
01/01/2021AmericaMexico110
01/01/2021AsiaChina160
01/01/2021AsiaJapan100

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

 

DateCountryMetricValue
01/01/2021USRisk of Cancer0.05
01/01/2021MexicoRisk of Cancer0.02
01/01/2021CanadaRisk of Cancer0.024
01/01/2021ChinaRisk of Cancer0.053
01/01/2021JapanRisk of Cancer0.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.

Need Help
  • CNENFRNL's avatar
    CNENFRNL
    5 years ago

    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's avatar
    v-luwang-msft
    5 years ago

    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

3 Replies

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    5 years ago

    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's avatar
    v-luwang-msft
    Community Support
    5 years ago

    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