Subtotal error

[primolee]

Hello everyone,

After all this time I use BI, this is the first time seeing such weird thing...

Left side is my data, there are only 3 rows and it is integer type.  Right side is report view, and as you see subtotal is 12599999.  I tried viewing min and max value, they are all the same 4200000.  Why is this happening?  There is no calculation in report view, calculations are done in Power Query and it is 100% integer type.

Is there a way to fix this? XDDD

Thank you!

Best regards,

David

[amitchandak]

@primolee , what is the data type. If it decimal change t whole number and check

[primolee]

It is integer (whole number), and there is no decimal at all.  Original data came from Excel and it is 4200000 in each row.

[harshnathani]

Hi @primolee ,

Do you have the relationship established with Date Table.

I think the relationship is missing.

Regards,

Harsh Nathani

[primolee]

Hello @harshnathani ,

Funny, there is no date column in this table.  However, it has a relationship link to another table with Date column.

When I remove the relationship, subtotal is correct.  However, this relationship is a must so I have to keep the relationship.

You can think of this table as a monthly budget table.  I have another table containing daily spending.  I am linking these two tables using Year/Month.

How should I solve this?  Thank you.

Best regards,

David

[harshnathani]

Hi @primolee ,

Which table is the Year/Month Colum which you have shown in the screenshot above. 

Can you share your .pbix file.

Regards,

Harsh Nathani

[primolee]

Year/Month in the image is from monthly budget table.  And I have another table just for Year/Month.

Monthly Budget

       |    <- 1 to 1, Bi-directional

Year/Month

       |    <- 1 to N, Bi-directional

Daily Spending

In the image above, I am pulling both Year/Month and Est. Imp. Per Month from the same table "monthly budget" and there is no other column from other tables.

Just now, I changed the second relationship to single-direction, and the subtotal is correct...  I don't understand...  Even if I don't use any columns from Daily Spending table or Year/Month table, subtotal is wrong...  Shouldn't subtotal simply be adding all rows together?

And here is another problem:

 

I used Buget from Cue Table as my Max value and spending from daily spending as value, max value shown is still 12599999.

This is simply driving me nuts... XD

[v-lionel-msft · Accepted solution]

Hi @primolee ,

I have not reproduced your problem, but I think you have two ways to solve this problem now.
1. Check the data model and recreate the table relationships.
2. Create a measure and add it to the table visual.

Dealing with Measure Totals 

Do you mind sharing your .pbix file?

Best regards,
Lionel Chen

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

[primolee]

Hello @v-lionel-msft 

Because there are many sensitive data, I tried to recreate the same scenario with very simplified data.  However, everything is perfect and I cannot recreate the error that I encountered previously.  Maybe because there are more relationships involved in my original case.

Let me try to play around with the data.

Thank you so much for your help!