Fill empty cell in Matrix with standard

[WillyW]

Hi all,

I have a Matrix in Power BI which shows cost per day of resources, summed up from a booking table.

Yet if an action starts day a and ends day c I do not have a value in day b. Therefore I added a "CostPerDay" column to my 'Date' table which is delivers the cost if the regular cost cell is empty.

My problem is that the subtotals per resource do not sum up the "CostPerDay" which I inserted into the empty lines, only the real cost values:

I already tried several ways for the "Cost"-Measure, but they don't change the behaviour:

IF( ISBLANK(SUM(BookingTable[Cost])),
SUM('Date'[CostPerDay]),
SUM(BookingTable[Cost]))

IF( ISBLANK(SUM(BookingTable[Cost])),
SUM('Date'[CostPerDay]),
SUM(BookingTable[Cost]))

Any ideas how I can fix this?

[amitchandak · Accepted solution]

@WillyW , Try like

sumx( values('Date'[Date]),IF( ISBLANK(SUM(BookingTable[Cost])),
SUM('Date'[CostPerDay]),
SUM(BookingTable[Cost])))

[parry2k · Accepted solution]

@WillyW use sumx

SUMX ( VALUES ( 'Date'[Date] ), 
VAR __s = CALCULATE(SUM(BookingTable[Cost]))
RETURN
IF( ISBLANK(__s),
CALCULATE(SUM('Date'[CostPerDay])),
__s
)
)

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[WillyW]

Thanks guys!   PERFECT!

Both work 😄

[amitchandak · Accepted solution]

@WillyW , Try like

sumx( values('Date'[Date]),IF( ISBLANK(SUM(BookingTable[Cost])),
SUM('Date'[CostPerDay]),
SUM(BookingTable[Cost])))