Measure is causing incorrect Grand Total

[JacktheYeti]

I'm using the following measure to calculate the total Cost per Unit allocated:

'Total $ Amount = SUM('Campaign'[CostPerMasterKey]) * SUM('RPT_CampaignOrderMasterKeyCounts'[TotalMasterKeysCount])'

Bundle	Cost Per Unit	Total Units Allocated	CPU * Total Units
Bundle 1	$6.00	318164	$1,908,984.00
Bundle 2	$6.50	31413	$204,184.50

Output:

The Grand Total the measure returns is $4,369,712.50 which is incorrect. I expected a Grand Total of $2,113,168.50. I'm not sure what's causing this issue, any help is appreciate in understanding the errors of my ways 🙂

[FrankAT · Accepted solution]

Hi @JacktheYeti 

your formula should read:

Total $ Amount = SUMX('Campaign', 'Campaign'[CostPerMasterKey] * RELATED('RPT_CampaignOrderMasterKeyCounts'[TotalMasterKeysCount]))

if both table have an active relationship.

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[JacktheYeti]

This did the trick, thanks very much. I'll need to read up on the RELATED and SUMX functions.

Warm regards,

Jack

[darentengmfs]

Hi @JacktheYeti 

You need to use SUMX instead of SUM.

Refer to the article: https://community.powerbi.com/t5/Desktop/SUMPRODUCT-in-excel-but-what-is-it-in-BI/td-p/129047