Grand total incorrect while subtracting two columns

[akj2784]

Hi All,

I have two tables A and B with columns A1 and B1 respectively.

Table A :-

A1

50

10

10

70

80

Table B :-

B1

60

10

20

30

10

Now, My requirement is to check :- if(A1-B1 < 0, 0, A1-B1)

So the expected result with a measure C := if(A1-B1 < 0, 0, A1-B1) , has to be :-

A1 B1 C

50 60 0

10 10 0

10 20 0

70 30 40

80 10 70

-------------

220 130 90

Now the individual row result is fine, the issue is with grand totals in the end. The expected results for column C here is 70+40 = 110, However, it subtracts the sum(A1) and sum(B1) i.e. 220-130 and shows 90.

How can this be corrected? Please note that the A1 and B1 are two different fact tables and we can drag any common dimensional data point in thr report.

I have tried using SUMX() instead of SUM but that didn't work either. For the grand total row, how can I filter two tables at the same time and do a subtraction row-wise and then add them?

I hope the question is clear. Please let me know if I need to explain it further.

Regards,

Akash

[v-danhe-msft]

Hi @akj2784,

From your description, I want to know the relationship between the two tables. If you just want to use these two single tables, I suggest you to merge the two columns and create a calculated column instead of a measure, you can refer to below steps:

1.Add an index column to both columns A1 and B1.

2.Merge the two tables and delete the extra index columns.

3.Apply it and create a calculated column to calculate the difference.

4.Create a Table visual and add the related fields. Now you can see the result.

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/6zscykgggqqp4p4/Grand%20total%20incorrect%20while%20subtracting%20two%20co...

Regards,

Daniel He

[akj2784]

thanks for the reply. These two Fact tables are joined by a dimension on a Foreign Key. So we cannot merge the table. They both are of different grain.

Any other suggestion which can help me create the metric without merging these two tables.

[Zubair_Muhammad]

@akj2784

Please see if this post helps you out

http://www.excelnaccess.com/incorrect-total-for-a-measure/

[akj2784]

I have the metric coming from two different tables and have 3-4 other dimensions to slice and dice the metric.

I think I already tried the given approach and that did not help.