Hierarchy Level Gives Incorrect Result for Weighted Average Calculation

[djdonnel]

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 incorrect at the chemical class level e.g. 58.92 for Organic Parameters. The value of 58.92 is the sum of weighted averages for the chemicals in the class i.e. 58.92 = 0.10 + 20.00 + 0.05 + ... + 6.70. So it appears to be adding the weighted averages of each chemical rather than taking the average overall.

I have chemical results in a Results table and the weighting factor in a Samples table. The relationship columns are both named [Pool Number Key]. This is my DAX formula for the weighted average. 

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

If I pull out the lower level to just show Chemical Class it is wrong on the rows and wrong on the total. 

EDIT: I have narrowed down the issue to this part of the formula.

SUM ( 'Samples'[Weight] )

At the chemical level, there are 24 samples for the region and the SUM ( 'Samples'[Weight] ) equals the sum of weights for 24 samples. At the class level the calculation is using SUM ( 'Samples'[Weight] ) for 24 samples again but it should be multiplied by 12 chemicals in the class (24 samples * weight for each of the 12 chemicals). The denominator in the weighted average calculation is off by a factor of how many chemicals are in the class. 

A manual check gives the right answer. 58.92 / 12 = 4.91 which is the correct weighted average for the Organic Parameters class.

[v-jiascu-msft]

Hi @djdonnel,

Can you share a dummy sample? The pbix file would be great. I think the DAX functions, like Summarize, Averagex, Hasonevalue, could help.

Best Regards,

Dal

[djdonnel]

 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?