DAX measure needed to calculate grand total of another measure

[LLGreen]

First off, here is what I'm working with:

The Date_EOM field is from an unrelated date table, while the Customer column and the three measures (Revenue_True, Revenue_True_Prev, and Revenue_Full_Churn_Detail) reside in another table. They all do what I need them to, but I can't seem to come up with a measure to get the Grand Total of Revenue_Full_Churn_Detail. I've tried variations using HASONEVALUE and SUMX (with and without using VALUES) and I can't get one that matches the total I get when adding the values in Excel. If it was just the Customer column and then the measures or just the Date_EOM column and the measures the HASONEVALUE would work perfectly. But having the data broken down by Customer and Date_EOM is giving me fits.

Can anyone point me toward a measure that will give me an accurate Grand Total of the values from the Revenue_Full_Churn_Detail measure?

[v-zhouwen-msft · Accepted solution]

Hi danextian ,thanks for the quick reply, I'll add more.

Hi @LLGreen ,

Regarding your question, are you trying to show the sum here?

Try this

MEASURE =
VAR _isTotalRow =
    SELECTEDVALUE ( 'Table'[Customer] )
VAR _table =
    SUMMARIZECOLUMNS (
        'Table'[Customer],
        'DateTable'[Date_EOM],
        "Result",
            IF (
                AND ( [Revenue_True] = 0, [Revenue_True_Prev] <> 0 ),
                [Revenue_True] - [Revenue_True_Prev],
                0
            )
    )
RETURN
    IF (
        ISBLANK ( _isTotalRow ),
        SUMX ( _table, [Result] ),
        IF (
            AND ( [Revenue_True] = 0, [Revenue_True_Prev] <> 0 ),
            [Revenue_True] - [Revenue_True_Prev],
            0
        )
    )

Best Regards,
Wenbin Zhou

[LLGreen]

Thank you SO much, both of you, for getting me out of a tight spot. @v-zhouwen-msft I tried your solution first (since it was the first I saw) and it works perfectly. I will say good things about both you and @danextian for the rest of my days.

[danextian]

Hi @LLGreen 

What formulas have you tried to get the total?

What is the formula used in Revenue_Full_Churn_Detail?

[LLGreen]

Here's the measure for Revenue_Full_Churn_Detail: 

Revenue_Full_Churn_Detail = IF(AND([Revenue_True]=0,[Revenue_True_Prev]<>0),[Revenue_True]-[Revenue_True_Prev],0)

As for what I've tried, I've tried just about everything I was able to find online. I've tried things such as 

IF(HASONEVALUE(<Customer>),[Revenue_Full_Churn_Detail],SUMX(<table>,[Revenue_Full_Churn_Detail])

I've tried a similar measure using Date_EOM instead of Customer, but was never able to do the equivalent of

AND(HASONEVALUE(<Customer>),HASONEVALUE(Date_EOM))

I've even tried things along the lines of 

SUMX(UNION(VALUES('Table1'[Customer]),VALUES('Table2'[Date_EOM])),[Revenue_Full_Churn_Detail])

Each one has given a different answer, but none have been correct. I'm not sure what else to look at.

[danextian]

hi @LLGreen,

Your Revenue_Full_Churn_Detail formula will not work because at the total level [Revenue_True] is not equal to 0 and at the date level, both can be blank or not blank but not zero (that's how your screenshot appears to me). Your SUMX formulas will not work either because the evaluation is by customer and not by date. If you want SUMX  to evaluate your churn logic by date and customer, you must include them both in the table expression which of course cannot be done with VALUES as it accepts a single column only.

SUMX (
    ADDCOLUMNS (
        SUMMARIZE ( 'table', 'dates'[date_EOM], customer[customer] ),
        "@churn",
            IF (
                ISBLANK ( [Revenue_True] ) && NOT ( ISBLANK ( [Revenue_True_Prev] ) ),
                [Revenue_True] - [Revenue_True_Prev]
            )
    ),
    [@churn]
)