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 #### The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.  