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.
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.
Solved! Go to 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]
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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
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
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]
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
96 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |