Incorrect total displayed

[Anonymous]

Hello! 🙂

I need your help and would appreciate it.

First of all I show you the data model: (not the whole model, just the part we are interested in):

The problem is that the total is not displayed correctly. (If we sum up all the rows, we will see that it exceeds the total displayed at the table bottom).

I'm using the measure:  

Loans = SUM(CirculationFact[Count_Checkout])

Can someone explain why this is happening and how I can fix it?

Thanks 🙂

[Anonymous]

Can someone explain me why does a simple SUM not work?
Is it somehow implacable that I have that bridge table in the data model?

Here I attached the pbix file: https://files.fm/f/emwb7m5fy

[Anonymous]

Hi @Anonymous ,

According to your data model, I know that "BibliographicSubjectDim" and "CirculationFact" tables are not connected directly, and both tables are many side in their relationship. So in your SUM calculation you will get wrong subtotal. You need to use SUMX() to sum the measure based on the column in "BibliographicSubjectDim".

Best Regards,
Rico Zhou

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

[HoangHugo]

Hi, total row be calculated base on underlying data, in your case, watn to sum all row of table, try this one

Loans = SUMX(SUMMARIE([Name]), SUM(CirculationFact[Count_Checkout]))

[Anonymous · Accepted solution]

It works now. I replaced the: SUM(CirculationFact[Count_Checkout]) with [Loans] measure in the SUMX and now it's ok.

Now the correct measure is: 

NEW_LOANS = SUMX(SUMMARIZE(BibliographicSubjectDim,BibliographicSubjectDim[Name]), [Loans])

 Thank you for you help.

[Anonymous]

Hi @HoangHugo,

Thank you for your response. Unfortunately it doesn't work ok. The amount now is much higher than expected.
I used:  

Loans2 = SUMX(SUMMARIZE(BibliographicSubjectDim,BibliographicSubjectDim[Name]), SUM(CirculationFact[Count_Checkout]))

Do you have another idea?