Forum Discussion

HCBD13's avatar
HCBD13
Regular Visitor
1 year ago
Solved

Use overall value for blank rows

Hi, so in my dimension table, I have an ID which does not exist to a connected fact table, thus showing blank to a matrix visual. Is there a way to use the overall value calculated by a measure for blank rows?

Blank rows

fill those blank columns with the overall value: 

 

Need Help
  • FreemanZ's avatar
    FreemanZ
    1 year ago

    hi HCBD13 ,

     

    try like:

    new measure =

    COALESCE(

       [revenue%],

       CALCULATE(

            [revenue%],

            ALL(dimtable[ID])

        )

    )

  • Anonymous's avatar
    Anonymous
    1 year ago

    Thanks for the reply from FreemanZ, please allow me to provide another insight.
    Hi HCBD13 ,

    Please try the following measure.

    Measure = IF(
    SUM(FactTable[Revenue%])=BLANK(),
    CALCULATE(SUM(FactTable[Revenue%]),ALL(DimID[ID])),
    SUM(FactTable[Revenue%])
    )


    Please see the attached pbix for reference.

    Best Regards,
    Dengliang Li

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

2 Replies

  • FreemanZ's avatar
    FreemanZ
    Super User
    1 year ago

    hi HCBD13 ,

     

    try like:

    new measure =

    COALESCE(

       [revenue%],

       CALCULATE(

            [revenue%],

            ALL(dimtable[ID])

        )

    )

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Thanks for the reply from FreemanZ, please allow me to provide another insight.
    Hi HCBD13 ,

    Please try the following measure.

    Measure = IF(
    SUM(FactTable[Revenue%])=BLANK(),
    CALCULATE(SUM(FactTable[Revenue%]),ALL(DimID[ID])),
    SUM(FactTable[Revenue%])
    )


    Please see the attached pbix for reference.

    Best Regards,
    Dengliang Li

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