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

[zylawys]

Hello,

I have a very simple table:

Name	Cost	Projected
Job 1	100	0
Job 2	100	150

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.

[mattbrice]

Try:

=
SUMX (
    VALUES ( Sheet1[Name] ),
    IF (
        CALCULATE ( SUM ( Sheet1[Projected] ) ) = 0,
        CALCULATE ( SUM ( Sheet1[Cost] ) ),
        CALCULATE ( SUM ( Sheet1[Projected] ) )
    )
)

[zylawys]

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]

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? 

[zylawys]

I guess I should have spcified that. The last column in the picture is the measures. 

Name	Cost	Projected
Job 1	100	0
Job 1	100	0
Job 1	150	0
Job 1	0	150
Job 1	0	100
Job 1	0	160
Job 2	200	0
Job 2	260	0
Job 2	200	0
Job 2	0	250
Job 2	0	230
Job 2	0	200

[v-sihou-msft]

@zylawys

In this scenario, since you need to have those calculated values aggregated, you should use calculated column instead of measure. Otherwise, it will always evalulate entire table on Grand Total level. So for your formula:

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

It will check the SUM(Sheet1[Projected]) for entire table first, if it's not 0, it will return the SUM(Sheet1[Projected]) of entire table as well.

Regards,

[mattbrice]

So you are saying 'Name', 'Cost', and 'Projected' are all NOT measures, but row values?  

The reason you are getting results the results you are is keep in mind Dax is an aggregation language.  Totals are not the sum of the rows above it, but rather have their own filter context.  

Having said that, I am not sure how your visual below can be.   There should not be rows where the values duplicate on a row basis.  For example, not sure how you got two rows where Name = Job 1, Cost = 100 and Projected = 0.