Grand Total for relu functions

[Ffitzpatrick47]

I've found dax is not super great at aggregate functions because you basically have to rebuild it for grand total purposes (and referencing them).  Here's an example on grand totals I can't get right.

I don't know why there's a pencil in the way.  Maybe there won't be after I'm done with this post. Just in case, that last column is relu, and it's basically max([in]+[cr],0), except there are no obvious max functions in dax so I used if statements rather than look up an esoteric one.  The grand total should be 09, but dax just evaluates the grand total as if a and b aren't filtered, rather than adding the results of a and b evaluated seperately.  How do I fix this?  I have a sample spreadsheet,  where would I upload it?  Oh, I know.  Google!

https://drive.google.com/file/d/1KM7uJBz2i8HKlH8UCRSMBUcoQ0KJ5tnM/view?usp=sharing

[Ffitzpatrick47]

Yes, do what you need to do, just deliver an answer where the grand total is 9

I see what you're doing with the count rows to detect whether the displayed row is a grand total or not (which in any other language is a hack), but even then you can't deliver the number 9 becauise sum([amt]) isn't it, it's a sum of the amt on a row by row basis and I want the sum based on the total amt on an aggregated basis as transformed by the relu function.  Please solve that, if there is a solution

[v-shex-msft]

Hi @Ffitzpatrick47,

I think you need to add a condition to filter on total row level and write a specific formula for this.

Measure = 
IF (
    COUNTROWS ( Table1 ) = COUNTROWS ( ALLSELECTED ( Table1 ) ),
    "Total row",
    IF (
        COUNTROWS ( Table1 )
            = COUNTROWS ( FILTER ( ALLSELECTED ( Table1 ), [c] = MAX ( [c] ) ) ),
        MAX ( SUM ( [amt] ), 0 ),
        IF (
            COUNTROWS ( Table1 ) = COUNTROWS ( ALLSELECTED ( Table1 ) ),
            0,
            SUM ( Table1[amt] )
        )
    )
)

 

Regards,

Xiaoxin Sheng

[Ffitzpatrick47]

This formula wont' work because it's not working on the rows in an aggregate.  You'll know if it works when instead of words "Total row", the answer is the number 9.  Even if you didn't do the total row thing, your answer for the total row calculation would be wrong, because it's not sum(Table1[sAmt]), it's a relu function in the aggregate

[v-shex-msft]

Hi @Ffitzpatrick47,

>>Even if you didn't do the total row thing, your answer for the total row calculation would be wrong, because it's not sum(Table1[sAmt]), it's a relu function in the aggregate

As you said, current these specific calculations only calculate on summarized records.

Notice: it will try to calculate on summary records instead drill and calculate on row contents.

In my opinion, I'd like to add a variable to store summary table with specific calculate result and look for correspond result to replace the default total row level result.

Regards,

Xiaoxin Sheng