Columnar Total is displaying zero (0), instead of the real sum of the column

[fmb]

I can't get a column's total to add up correctly. The column comes from a measure and it always returns 0 as total.

Maybe if I explain a bit further someone might shed some light on why this is happening and how should I code the correct DAX syntax.

I have a details table that contains revenue by client by date. I have 2 columns (which have been summarized): FY17Revenue and FY18Revenue. Then I created a calculated column that calculates the difference from FY18 - FY17. For certain rows, I will have positive and negative values.

This is the DAX formula:
FY17FY18Churn = CALCULATE(SUMX(RPT_CHURN, RPT_CHURN[FY18Revenue]-RPT_CHURN[FY17Revenue]))

From this result I need to add an IF condition that lets me return 0 if the difference's outcome is positive, otherwise it should return the difference.

For example:
If FY17Revenue = 1,751,496 and FY18Revenue = 987,545 then the result would be -819,868.
If FY17Revenue = 1,207,629 and FY18Revenue = 1,243,964 then the result would be 36,334. But if I apply the IF condition, it should instead display zero (0).

So I used the following formula:
FY17FY18ChurnUpdated = IF([FY17FY18Churn] > 0, 0, [FY17FY18Churn])

After this is where things get really confused. I see that some rows are 0 (based on the condition if the difference between FY18Revenue and FY17Revenue is greater than 0 then it should display 0, otherwise show the difference), and others are negative. From the standpoint on how the numbers should display it is correct! The problem lies in the total where now it is shown as 0 as well. I tested the formula and instead of returning 0, I replaced it with an arbitrary number as 999. Well the total now shows 999.

So my question is... how can make that new column do the following 2 things:
1) display 0 if the difference between FY18Revenue and FY17Revenue is greater than 0.
2) it must calculate the total on based on the numbers that are result of the condition in the previous step.

Any help is really appreciated. I've attached a screenshot of the table (churn table).

Thanks in advance.

[v-shex-msft]

HI @fmb,

Is there any index column in your table? If not exists index column, it is impossible to achieve requirement 2.(Index of client)

FY17FY18ChurnUpdated =
SUMX (
 ADDCOLUMNS (
 SUMMARIZE (
 FILTER ( ALL ( Table ), Table[IndexColumn] <= EARLIER ( Table[IndexColumn] ) ),
 [Client],
 "TotalFY17", SUM ( Table[FY17Revenue] ),
 "TotalFY18", SUM ( Table[FY18Revenue] )
 ),
 "FY17FY18Churn", IF ( [TotalFY18] - [TotalFY17] > 0, 0, [TotalFY18] - [TotalFY17] )
 ),
 [FY17FY18Churn]
)

If above not help, please share some details information to help coding formula.

Regrads,

Xiaoxin Sheng

[fmb]

Hi @v-shex-msft,

Thanks for helping with this. I tried your code though I had to "tweak" it a bit in order to accomplish the correct total of the column to not show 0.

This is the formula that I used:

FY17-FY18 Difference By Client=
SUMX (
    ADDCOLUMNS (
        SUMMARIZE (
            RPT_CHURN,
            RPT_CHURN[Client],
            "TotalFY17", SUMX ( RPT_CHURN, [FY17 Revenue M] ),
            "TotalFY18", SUMX ( RPT_CHURN, [FY18 Revenue M] )
        ),
        "FY17FY18ChurnNew", IF ( [TotalFY18] - [TotalFY17] > 0, 0, [TotalFY18] - [TotalFY17] )
    ),
    [FY17FY18ChurnNew]
)

Now I have a question, is there a way to apply the grouping in a "dynamic" way? As far as I can see, the above code is summing the difference between [TotalFY18] and [TotalFY17] when it's grouped by [Client] (in the SUMMARIZE part -> RPT_CHURN[Client]). For example, what happens if the I want to add 2 more fields as: [Group] and [Manager] to the table? The column is always showing the total based on [Client], because this is what the code states.

Or... should I create a formula for each case? In other words, something like below so it can do the correct sum:

FY17-FY18 Difference By Product and Manager =
SUMX (
    ADDCOLUMNS (
        SUMMARIZE (
            RPT_CHURN,
            RPT_CHURN[Product], RPT_CHURN[Manager],
            "TotalFY17", SUMX ( RPT_CHURN, [FY17 Revenue M] ),
            "TotalFY18", SUMX ( RPT_CHURN, [FY18 Revenue M] )
        ),
        "FY17FY18ChurnNew", IF ( [TotalFY18] - [TotalFY17] > 0, 0, [TotalFY18] - [TotalFY17] )
    ),
    [FY17FY18ChurnNew]
)

I tried the above code and looks like it worked.

Thanks so much for your help.

[v-shex-msft]

HI @fmb,

Current it impossible to dynamic grouping column amount which used in summarize function, you need to define them before used in visuals.

Regards,

Xiaoxin Sheng

[fmb · Accepted solution]

Hi @v-shex-msft,

Ok I understand. Hope this gets added to future release.

Thanks again for your support.