Forum Discussion

djdonnel's avatar
djdonnel
Regular Visitor
8 years ago

Hierarchy Level Gives Incorrect Result for Weighted Average Calculation

I have a weighted average calculation that works properly at one level of granularity but not at the next level up. The result is correct on the chemical level e.g. 0.81 for Bisphenol A, but incorrec...
Need Help
djdonnel's avatar
djdonnel
Regular Visitor
8 years ago

 I have found a solution by replacing 

 

 

SUM ( 'Samples'[Weight] )

 

with

 

 

SUMX ( 'Samples', 'Samples'[Weight]*DISTINCTCOUNT( 'Results'[Chemical Name] )

 

 

The complete formula is

 

 

=CALCULATE (
    SUMX ( 'Results', [Results numeric] * RELATED ( 'Samples'[Weight] ) )
        / SUMX (
            'Samples',
            'Samples'[Weight] * DISTINCTCOUNT ( 'Results'[Chemical Name] )
        ),
    CROSSFILTER ( 'Results'[Pool Number Key], 'Samples'[Pool Number Key], BOTH )
)

 

Here's a comparison of the formula giving incorrect result vs. formula giving the correct result. I have also included the respective denominator calculation for the weighted average. As you can see, the SUM('Samples'[Weight]) result is the same at both levels of the hierarchy because the 'Samples' table is not being expanded by the number of chemicals in the class. So each sample's weight is being included only once when it should be included 12 times (in the case of the Organic Parameters class). Changing the formula to SUMX('Samples','Samples'[Weight]*DISTINCTCOUNT('Results'[Chemical Name])) effectively arrives at the correct value.


Is there a better way to achieve the desired result?