Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Need help with calculating common data using Dax

Hi ,   I am new to Power bi. There is a requirement to calculate the average of amount for the common part numbers. I am not sure how to achieve it using dax query.   Table1:  Country Code ...
Need Help
  • johnt75's avatar
    johnt75
    4 years ago

    Make sure that there is no relationship between Table1 and Table2, then create the below measure

    Avg Amount = 
var benchmark = SELECTEDVALUE(Table1[Country Code])
return IF( NOT ISBLANK( benchmark),
    var benchmarkParts = CALCULATETABLE( VALUES(Table2[Part Number]), REMOVEFILTERS(Table2[Country Code]), Table2[Country Code] = benchmark)
    var currentCountryParts = VALUES(Table2[Part Number])
    return CALCULATE( AVERAGE(Table2[Amount]), INTERSECT( benchmarkParts, currentCountryParts ) )
)

    Add that to a table visual with the country code column from Table2

johnt75's avatar
johnt75
Icon for Super User rankSuper User
4 years ago

Make sure that there is no relationship between Table1 and Table2, then create the below measure

Avg Amount = 
var benchmark = SELECTEDVALUE(Table1[Country Code])
return IF( NOT ISBLANK( benchmark),
    var benchmarkParts = CALCULATETABLE( VALUES(Table2[Part Number]), REMOVEFILTERS(Table2[Country Code]), Table2[Country Code] = benchmark)
    var currentCountryParts = VALUES(Table2[Part Number])
    return CALCULATE( AVERAGE(Table2[Amount]), INTERSECT( benchmarkParts, currentCountryParts ) )
)

Add that to a table visual with the country code column from Table2

Anonymous's avatar
Anonymous
Not applicable
4 years ago

It works. Thank you !!!