Forum Discussion

zylawys's avatar
zylawys
Advocate II
9 years ago

Sum Rows in a Column which was created by a measure Help!

Hello,

 

I have a very simple table:

 

NameCostProjected
Job 11000
Job 2100150

 

I created a measure which will make a new column where if there is a 0 take the cost column, if its greater than 0 that the projected column.

 

Total Projected = IF(SUM(Sheet1[Projected]) = 0, SUM(Sheet1[Cost]), SUM(Sheet1[Projected]))

 

 

This works, however the Total is only showing the number in the projected column so my line item is 150 not 250. I see that it is taking the sum of the column, but how can I get it to sum the rows off the condition?

 

Any help would be a lot of help.

Need Help

6 Replies

  • mattbrice's avatar
    mattbrice
    Solution Sage
    9 years ago

    Try:

     

    =
SUMX (
    VALUES ( Sheet1[Name] ),
    IF (
        CALCULATE ( SUM ( Sheet1[Projected] ) ) = 0,
        CALCULATE ( SUM ( Sheet1[Cost] ) ),
        CALCULATE ( SUM ( Sheet1[Projected] ) )
    )
)
    • zylawys's avatar
      zylawys
      Advocate II
      9 years ago

      Thanks for the try but I do get the same results.

       

      I switched up the data a bit but it still seems to just tally Projected column. not really sure where the 200 and 400 are coming from in the total projected column.

      • mattbrice's avatar
        mattbrice
        Solution Sage
        9 years ago

        ok - data visualization was different from what your original sample showed.   I assumed you were grouping info by Name.   Only one row for 'Job 1', one row for 'Job 2', etc.

         

        In your latest visualization, which are the row values, and which are meaures?