Search code examples
powerbidax

Replace blank cells in matrix with 0

I am trying to display a matrix in power bi but some cells are appearing as blank instead of 0.

I created a calculated table and created a measure inside that table to use as the Values parameter for the matrix that looks like this:

Value = IF(ISBLANK(SUM(Sell[Value])), 0, SUM(Sell[Value]) + 0)

OR

Value = SUM(Sell[Value]) + 0

I saw both of these options when searching for a way to achieve my result set but I don't get my expected output. Is there a way to achieve this?

SAMPLE:

main table:

Counterparty  Product  Deal  Date          Value
foo           bar      Buy    01/01/24     10.00
foo           bar      Buy    01/01/24     10.00
foo           bar      Sell   01/01/24     10.00
foo           bar      Sell   01/01/24     10.00
fizz          bar      Buy    01/01/24     10.00
fizz          bar      Buy    01/01/24     10.00
fizz          buzz     Sell   01/01/24     10.00
fizz          buzz     Sell   01/01/24     10.00

Calculated table

Sell = FILTER('Table', 'Table'[Deal] = "Sell")

Counterparty  Product  Deal  Date          Value
foo           bar      Sell   01/01/24     10.00
foo           bar      Sell   01/01/24     10.00
fizz          buzz     Sell   01/01/24     10.00
fizz          buzz     Sell   01/01/24     10.00

I create a matrix for each table using Counterparty as the rows, Product as the columns, and I create the above measures for the value parameter which I create in each table.

OUTPUT for sell table:

Counterparty   BAR      BUZZ
foo           20.00   (blank)
fizz         (blank)   20.00

EXPECTED OUTPUT for sell table:

Counterparty   BAR      BUZZ
foo           20.00      0
fizz            0     20.00
Solution

  • I don't understand why you are using calculated tables but I will follow the way you want to achieve your result.

    The trick here is that you want to show 0 instead of blank when there is no combination between Counterparty and Product.

    I created a CC table with the possible combinations :

    Combinations = 
CROSSJOIN(
    VALUES('Table'[Counterparty]),
    VALUES('Table'[Product])
)

    enter image description here

    Then for the measure, I try to sum the values but only for rows where the combination matches the current Counterparty and Product being evaluated :

    Value = 
VAR CurrentCounterparty = SELECTEDVALUE(Combinations[Counterparty])
VAR CurrentProduct = SELECTEDVALUE(Combinations[Product])
VAR TotalValue = CALCULATE(
    SUM(Sell[Value]),
    Sell[Counterparty] = CurrentCounterparty,
    Sell[Product] = CurrentProduct
)
RETURN IF(ISBLANK(TotalValue), 0, TotalValue)

    In the matrix you need to use the Counterparty and Product from the combination table :

    enter image description here

    If you want to use a measure :

    Value = 
IF(
    ISBLANK(SUM(Sell[Value])),
    0,
    SUM(Sell[Value])
)