Forum Discussion
Solved
Incorrect Row Subtotal when using coalesce
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
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] )
3 Replies
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] )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
