Incorrect Row Subtotal when using coalesce

[JakeJack]

Hi all

I'm trying to create a cash flow using the matrix visual.  

I have most things working except for subtotals - see below:

As you can see the subtotals For the Cash Flow from Operating Activities and Cash Flow from Financing Activities are incorrect.

The measures I'm using are:

CF Actuals = COALESCE([CF Value from PL],[CF BS Change])

CF Value from PL = IF([CF Select Category] in {209,3100,4000,705},[Total Value CY]*-1,[Total Value CY])

CF BS Change =

(IF([CF Select Category] in {209,3100,4000,705},[Balance Sum]*-1,[Balance Sum])-CALCULATE(IF([CF Select Category] in {209,3100,4000,705},[Balance Sum]*-1,[Balance Sum]),PARALLELPERIOD(Dates[CurDate],-1,MONTH)))

Total Value CY = sum('Sage PL'[val])*-1

Balance Sum = sum('Sage BS'[Val])

The reason I'm using the coalesce function is that the values are coming from 2 FACT table - 'Sage PL' and 'Sage BS'.

NB The subtotal are also not reflecting the change in sign either - the -195 should be 195.

Any help would be most appreciated .

Thanks

Jake 

[DataInsights · Accepted solution]

@JakeJack,

One approach is to use SUMX to iterate the dimension table that is used in matrix rows. Example measure:

CF Actuals with Correct Totals =
SUMX ( VALUES ( DimTable[Column] ), [CF Actuals] )

[JakeJack]

Many thanks - that worked a treat.  

It had me stumped so much that I was considering adding a specific cash flow FACT table.

Cheers

Jake

[DataInsights]

@JakeJack,

Glad to hear that worked, and that you avoided creating an additional fact table. 🙂