Dax formula: Non-standard "totals" problem?

[mrogersvtabsv]

Hi everyone,

First time poster here -- I'm new to DAX but decent with Excel. I'm trying to "translate" an Excel formula into DAX. I realize there are fundamental differences between using cells to describe things and using columns/filtering. 

The problem: I started with a formula that "picks" the right actual hours, the so-called "Actual Actuals." That is described by:

Column D = Wk3_Actual

Column E = Total Hrs

Column F = IF(NOT(ISBLANK($D11)),$D11,$E11)

If I blindly translate this to DAX without any thought, it becomes:

=IF(NOT(ISBLANK([Sum of Wk3_Actual])),[Sum of Wk3_Actual],[Sum of Total Hrs])

and the results are:

This gives the correct results for each row, except the total row. It doesn't feel like there are any filters (hasonefilter didn't seem to work very well).

Any ideas?

[v-yulgu-msft · Accepted solution]

Hi @mrogersvtabsv ,

Please refer to below measures:

Sum WK3_Actual = SUM(prt[WK3_Actual])
Sum Total Hrs = SUM(prt[Total Hrs])
Actual actuals = IF(NOT(ISBLANK([Sum WK3_Actual])),[Sum WK3_Actual],[Sum Total Hrs])
Final measure = SUMX(prt,[Actual actuals])

Best regards,

Yuliana Gu

[mrogersvtabsv]

Yuliana Gu, 

You have solved the non-standard "totals" problem! Thanks so much!

[Anonymous]

@mrogersvtabsv  - There are 2 different SUM functions in DAX: SUM and SUMX.

SUM evaluates the expression within a certain filter context. In your example, the total is evaluated separately - in other words, it simply has a different filter context. Each row has a smaller filter.

SUMX iterates each row within the filter context, evaluating the expression for each row and then sums them.

In this case, you want to use SUMX.

Your Measure = SUMX('Your Table', <Your Expression>)

Hope this helps,

Nathan

[mrogersvtabsv]

Hey there,

Thanks for the reply. Unfortunately, that was the first thing I tried and it doesn't appear to be a solution.

It produces the same result as before (correct rows/unexpected totals):

Week 3 (SUMX) =SUMX(prt,prt[Wk3_Actual])

Total Hrs (SUMX) =SUMX(susan, susan[Total Hrs])

Actual Actuals (SUMX) =IF(NOT(ISBLANK([Week 3 (SUMX)])),[Week 3 (SUMX)],[Total Hrs (SUMX)])

That 177.0 total in blue should be 374.3...

[Anonymous]

@mrogersvtabsv  - 

The final measure is still checking whether the Total of Week 3 is blank.

Also, I had incorrectly assumed that both hours values were coming from the same table.

The calculation should be something like the following:

NOTE: Since the values come from different tables, you will need to use the RELATED or RELATEDTABLE function to fill in the "<reference to..." portion.

Actual Actuals (SUMX) =
SUMX (
    prt,
    IF ( NOT ( ISBLANK ( prt[Wk3_Actual] ) ), prt[Wk3_Actual], <reference to the other table's column - they need to be related for this to work>)
)