Forum Discussion

WillyW's avatar
WillyW
Frequent Visitor
6 years ago
Solved

Fill empty cell in Matrix with standard

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?

Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    WillyW , Try like

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

  • parry2k's avatar
    parry2k
    6 years ago

    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.⚡

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    WillyW , Try like

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

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    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's avatar
      WillyW
      Frequent Visitor
      6 years ago

      Thanks guys!   PERFECT!

      Both work 😄