cancel
Showing results for
Did you mean:
Frequent Visitor

## 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

1 ACCEPTED SOLUTION
Super User

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] )``````

Proud to be a Super User!

3 REPLIES 3
Super User

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] )``````

Proud to be a Super User!

Frequent Visitor

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

Super User

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

Proud to be a Super User!

Announcements