Card total/table total doesn't match with the data

[Deelip]

Hi all,

I have seen this type of question on the forums. I tried to implement as per the forums but none of them got me to the solution. I am lost on this problem.

The sum of total cost is not matching with the total of the rows.

Info on model/tables/columns:

I only have one table and no date table in the model ( the requirement is only to display one KPI which is total cost). 

All the numercial columns are coming right from the source, so no measure/calculated columns are in the power bi.

Below is the data and I haven't summarized any of these columns and the requirement is not to summarize, however they want to see the sum of total cost. Total cost value is from (Quanity * total cost per unit). I have two filters applied on the report page which are shipped date and customer name. 

When I try to use the calculated column which is  Test total cost (Quanity * total cost per unit) and the summarize value comes at total of 237,473, but the actual total cost when I manually add up my Total cost column is 27,865 (this is what I am expecting to be).

**When i use summarize on the Test total cost column the values for both total cost and the Test total cost column values are not similar. How to deal with this problem where my sum of total cost should match to the filtered data on the report page.

Any help on this is greatly appreciated

Thank you!

[Ashish_Mathur · Accepted solution]

Hi,

You will have to use the SUMX() function as a measure.

Total = sum(Data[Revenue])

Measure = SUMX(VALUES(Data[Product]),[Total])

Hope this helps.

[Anonymous · Accepted solution]

Hello,Ritaf1983  and Ashish_Mathur ,thanks for your concern about this issue.

Your answers are excellent!
And I would like to share some additional solutions below.

Hi,@Deelip .I am glad to help you.

I think you can try Ashish_Mathur's suggestion.
Total columns ending up with incorrect calculations is a common problem in Power BI.
The possible reasons are as follows:
The column/row total has no context due to the lack of context, plus the calculation logic in the dax code itself is not simply a direct use of a single aggregation function, e.g.

column_test =SUM('Table'[column]);
measure_test=MAX('Table'[column]);

In general, this directly use a power BI aggregation function, Total column display data will also be directly using the calculation logic are data for aggregation operations.
If you use the summarize function and redefine a series of calculation environments in it, then the Total column in visual may not return the correct value.

You can try to create some calculation logic simple measure to replace the original error data.
For example, using the SUMX function

If you can share the pbix file which does not contain sensitive data and the corresponding code, then this will help you to solve your problem.

If you are unable to share your data file, hopefully the following articles will help.
URL:

Why Power BI totals might seem inaccurate - SQLBI
How to Fix Incorrect Totals in Power BI Tables (databear.com)
Solved: Help with incorrect totals in table and card visua... - Microsoft Fabric Community

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Deelip]

@Anonymous 

Thank you for sharing your detailed explanation. It explains me a lot better. I have identified the issue and it is resolved. 

[Ashish_Mathur · Accepted solution]

Hi,

You will have to use the SUMX() function as a measure.

Total = sum(Data[Revenue])

Measure = SUMX(VALUES(Data[Product]),[Total])

Hope this helps.

[Deelip]

@Ashish_Mathur 

Thank you for reviewing my quesiton and providing your solution on this. I got this fixed. 

[Ashish_Mathur]

You are welcome.

[Ritaf1983]

Hi @Deelip 

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

[Deelip]

Hi @Ritaf1983 

Sorry this is kind of interesting for me too. I understand the background of the issue and how it is grouping. It is not related to power bi or dax or anything its just the conceptual understanding of my source data. Thank you.