Matrix columns do not adding up to the grand total

[Bopana]

Hi,

I am working on preparing consolidated financials statements for a company that has three entities.   This involves converting the reporting currency of two of the entities to the third entities reporting currency.

The dataset has transaction level information. This means that to arrive at the balance sheet numbers, I will have to add transactions from inception to date.  To achive this I have used a running total.

Running Total USA in USD =

VAR LastVisibleDate =

MAX ( 'Dates Table'[Date] )

VAR FirstVisibleDate =

MIN ( 'Dates Table'[Date])

VAR LastDateWithSales =

CALCULATE (

MAX ( 'Trial balance'[Transaction Date] ),

REMOVEFILTERS () -- Use ALL ( Sales ) if REMOVEFILTERS () and ALL ()

-- are not available

)

VAR Result =

IF (

FirstVisibleDate <= LastDateWithSales,

CALCULATE (sum('Trial balance'[Transaction Amount]),

'Dates Table'[Date] <= LastVisibleDate,'Trial balance'[Country]="USA")

)

RETURN

Result

I have the exchange rates table from which I have looked up the latest exchange rate applicable to the cumulative total.

FxLatest = CALCULATE(max('USA Rates'[BS]),'Dates Table'[Date].[Date])

RelationshipsDifferences in matrix total

Based on my recalculations, I understand that for FY 20-21 while the columns show correct values i.e. Running Total USA in USD * 73.76 but the grand total seems to calculate differently i.e. Running Total USA in USD *75.1.

Any help will be appreciated.

[v-janeyg-msft]

Hi, @Bopana 

Simply share the code you write doesn't work and the screenshoots are vague. Can you share some sample fake data? So we can help you soon.

You can reference:

How to Get Your Question Answered Quickly - Microsoft Power BI Community

Best Regards

Janey Guo

[amitchandak]

@Bopana , Try this var like this and try

VAR Result =
CALCULATE (sum('Trial balance'[Transaction Amount]),
'Dates Table'[Date] <= LastVisibleDate,'Trial balance'[Country]="USA" && FirstVisibleDate <= LastDateWithSales )

[Bopana]

Sorry this does not seem to work. Marked it as solved incorrectly.