cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JacktheYeti
Helper I
Helper I

Measure is causing incorrect Grand Total

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

'Total $ Amount = SUM('Campaign'[CostPerMasterKey]) * SUM('RPT_CampaignOrderMasterKeyCounts'[TotalMasterKeysCount])'
 
BundleCost Per UnitTotal Units AllocatedCPU * Total Units
Bundle 1$6.00318164$1,908,984.00
Bundle 2$6.5031413$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 🙂

1 ACCEPTED SOLUTION
FrankAT
Super User II
Super User II

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)

View solution in original post

3 REPLIES 3
FrankAT
Super User II
Super User II

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)

View solution in original post

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

 

Warm regards,

Jack

darentengmfs
Super User I
Super User I

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

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors