Forum Discussion

ATREZISE's avatar
ATREZISE
Helper I
7 years ago
Solved

Display Blank as Zero

Hi All 

 

There appears to be lots of posts about this with the simplest way just to add 0 to a measure...

 

i.e. measure = SUM(totals[revenue]) + 0

 

However adding 0 seems to discard any filters on the underlying tables so from having a nice matrix which only shows data where there's some to show, it opens up all rows which makes the matrix more cluttered with data I don't wish to see.

 

For example:

 

Using the measure without 0 shows the data as below...

 

EMP     PROJECT    MONTH1    MONTH2    MONTH3

emp1   project1     1000.00       1500.00      1200.00

emp1   project2     2000.00

emp2   project1                         1300.00      1400.00

 

Using the measure with 0 shows the data as below...

 

EMP     PROJECT    MONTH1    MONTH2    MONTH3

emp1   project1     1000.00       1500.00      1200.00

emp1   project2     2000.00

emp2   project1                         1300.00      1400.00

 

Using the measure without 0 shows the data as below...

 

EMP     PROJECT    MONTH1    MONTH2    MONTH3

emp1   project1     1000.00       1500.00      1200.00

emp1   project2     2000.00

emp2   project3                         1300.00      1400.00

 

Using the measure after adding 0 shows the data like this...

 

EMP     PROJECT    MONTH1    MONTH2    MONTH3

emp1   project1     1000.00       1500.00      1200.00

emp1   project2     2000.00             0.00            0.00

emp1   project3           0.00             0.00            0.00

emp2   project1           0.00             0.00            0.00

emp2   project2           0.00             0.00            0.00

emp2   project3           0.00       1300.00      1400.00

 

So the formatiing is correct but I now see 3 x additional rows that I don't need to see as they have no relevant data. The promblem is amplified as we have hundreds of emplyees and project combinations.

 

Is there any way to overcome this?

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi ATREZISE 

    Create a measure

    Measure 4 =
IF (
    CALCULATE (
        SUM ( 'Table 4'[value] ),
        ALLEXCEPT ( 'Table 4', 'Table 4'[emp], 'Table 4'[project] )
    )
        = BLANK (),
    BLANK (),
    SUM ( 'Table 4'[value] ) + 0
)

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

9 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi ATREZISE 

    Create a measure

    Measure 4 =
IF (
    CALCULATE (
        SUM ( 'Table 4'[value] ),
        ALLEXCEPT ( 'Table 4', 'Table 4'[emp], 'Table 4'[project] )
    )
        = BLANK (),
    BLANK (),
    SUM ( 'Table 4'[value] ) + 0
)

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Maybe you can validate based on the sum of the measure YTD or any other datefilter (I'm assuming you are looking at a specific period of months).

     

    I.e.

     

    measure YtD = TOTALYTD( SUM(totals[revenue]), Date[Date])

measure = IF( ISBLANK([measure YtD]), BLANK(), SUM(totals[revenue]) + 0)

    se

    • ATREZISE's avatar
      ATREZISE
      Helper I
      7 years ago

      Hi

       

      Thanks for the suggestion but I get the same result - the filter is not recognised and all rows are returned.

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        I have tried to recreate your model (or a subset of it). I believe that it can work with the following measures:

         

        revenue (+ 0) = SUM( fact[Revenue]) + 0
revenue = IF( CALCULATE( [revenue (+ 0)]; ALL( 'Date')) = 0; BLANK(); [revenue (+ 0)])


        This way I am able to only show the employees who have an actual value for any project (as in the bottom picture)

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi ATREZISE 

     

    If one of the solutions worked would you then mark it as an accepted solution? :smileyhappy: