cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
alexa_0028
Resolver II
Resolver II

grand total in matrix not matching with sum of row totals

Hi All,

I have a complex model and my relationship is like below.
I am using inactive relationships as I have multiple relationships from same table to another table.

alexa_0028_0-1643329453852.png


I am having a matrix which shows correct value at each row and subtotals but it doesnt show the right grand total.

alexa_0028_1-1643329758622.png

My DASX formula is very big , like below.
Please help me to fix the grand total, I am having issues below i am having multiple relationships to same table.

IS all =

var IS_cac_sub_franchise = CALCULATE([Invoiced Sales (IS)],'C Account'[C. Account Code (CAC)]IN VALUES ('cac sub franchise'[Code Account]) ,'M Sales'[flag_sample]=0)

var IS_cac_minor = CALCULATE([Invoiced Sales (IS)],USERELATIONSHIP('cac minor'[Code Product],'Product'[bravo_minor_code]),'C Account'[C. Account Code (CAC)]IN VALUES ('cac minor'[Code Account]) ,'M Sales'[flag_sample]=0)

var IS_ekg_minor = CALCULATE([Invoiced Sales (IS)],USERELATIONSHIP('ekg minor'[Code Product],'Product'[bravo_minor_code]),'Product'[P. Comm Hier Lvl 3]<>"Attune Revision",'C Buying Groups'[C. Account Buying Group Code] IN VALUES ('ekg minor'[Code Account]) ,'M Sales'[flag_sample]=0)

var IS_ekg_subfranchise = CALCULATE([Invoiced Sales (IS)],USERELATIONSHIP('ekg sub franchise'[Code Product],'Product'[bravo sub franchise code]),'C Buying Groups'[C. Account Buying Group Code] IN VALUES ('ekg sub franchise'[Code Account]) ,'M Sales'[flag_sample]=0)

var IS_ekg_sku =
CALCULATE([Invoiced Sales (IS)],USERELATIONSHIP('ekg sku'[Code Product],'Product'[P. Code]),'C Buying Groups'[C. Account Buying Group Code] IN VALUES ('ekg sku'[Code Account]) ,'M Sales'[flag_sample]=0)

RETURN
SWITCH(TRUE(),
SELECTEDVALUE('all agreements'[Type])="cac minor",IS_cac_minor,
SELECTEDVALUE('all agreements'[Type])="ekg minor",IS_ekg_minor,
SELECTEDVALUE('all agreements'[Type])="cac sub franchise",IS_cac_sub_franchise,
SELECTEDVALUE('all agreements'[Type])="ekg sub franchise", IS_ekg_subfranchise,
SELECTEDVALUE('all agreements'[Type])="ekg sku", IS_ekg_sku,
IS_ekg_sku+IS_ekg_subfranchise+IS_ekg_minor+IS_cac_minor+IS_cac_sub_franchise
)



 

 

2 REPLIES 2
v-xiaotang
Community Support
Community Support

Hi @alexa_0028 

Have you solved this question with VahidDM's help? If you have solved the question, you can accept the answer helpful as the solution or share you method and accept it as solution, thanks for your contribution to improve Power BI.

If you need more help, please let me know.

 

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

Hi @alexa_0028 

 

This is a common issue, read this post to find the solution:

https://www.vahiddm.com/post/why-my-measure-returns-the-wrong-total

VahidDM_2-1643334754745.png

 

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: 
www.linkedin.com/in/vahid-dm/

 

 

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors