Forum Discussion

newpbiuser01's avatar
newpbiuser01
Icon for Helper V rankHelper V
2 years ago

Nulls in Matrix Table with Column and Row Headers

Hello,   I have a matrix table that shows the Total by ID and Year. To get the Total - I have  the following measure: Total = Var Abc = Sum(Price) Return if(isblank(Abc), 0, Abc)   When I put...
Need Help
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi newpbiuser01 

 

For your question, here is the method I provided:

 

Here's some dummy data

 

"Table"

 

Create a measure.

 

Measure = 
VAR abc = 
    CALCULATE(
        SUM('Table'[Price]), 
        FILTER(
            ALL('Table'), 
            'Table'[ID] = MAX('Table'[ID])
            &&
            'Table'[Year] = MAX('Table'[Year])
        )
    )
RETURN
IF(
    ISBLANK(abc), 
    0,
    abc
)

 

Here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

  • newpbiuser01's avatar
    newpbiuser01
    Icon for Helper V rankHelper V
    2 years ago

    Hi Anonymous ,

     

    Follow up question - this wouldn't work if I am using Field Parameters for my row and column selector and I can't use the row and column field in the calculate function. I apologize, I simplified my table in my original post, but I do have a table that uses field parameters so the row and column fields are variable. How do I show the null values as $0 in this case?

     

     

    The measure here is 

    Measure =
    VAR abc =  SUM('Table'[Price])
    RETURN
    IF( ISBLANK(abc),  0, abc )
     
    Thank you so much! I really appreciate any help.