Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
fmb
Regular Visitor

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

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).

 

CHURN.Table.pngThanks in advance.

1 ACCEPTED SOLUTION

Hi @v-shex-msft,

 

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

 

Thanks again for your support.

View solution in original post

4 REPLIES 4
v-shex-msft
Community Support
Community Support

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

 

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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] > 00, [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] > 00, [TotalFY18] - [TotalFY17] )
    ),
    [FY17FY18ChurnNew]
)

 

I tried the above code and looks like it worked.

 

Thanks so much for your help.

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi @v-shex-msft,

 

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

 

Thanks again for your support.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.