cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
trojii Frequent Visitor
Frequent Visitor

No Sum for Grand Total in a table using BLANK()

Dear Community,

You helped me once, so maybe you can help me again!

 

I'm calculating the difference of a measure between to date (e.g. "How many Followers did I loose/win compared to last month?").

Now, since no data is available for the current month, it shows the foundation of last month as the difference, which is wrong. I want it to be BLANK. If I do that, however, there is no grand total any more.

How can I solve this?

2018-03-23_11h27_43.png

 

My measures:

Calculating the difference:

Difference:=
CALCULATE (
    SUMX ( fact_netcustomers, fact_netcustomers[fnc_value] ),
    fact_netcustomers[dty_id_f] = 2
)
    - CALCULATE (
        SUMX ( fact_netcustomers, fact_netcustomers[fnc_value] ),
        fact_netcustomers[dty_id_f] = 2,
        PARALLELPERIOD ( Datum[Datum], -1, MONTH )
    )

Version with BLANK():

Difference Blank :=
VAR var_value =
    CALCULATE (
        SUMX ( fact_netcustomers, fact_netcustomers[fnc_value] ),
        fact_netcustomers[dty_id_f] = 2
    )
        - CALCULATE (
            SUMX ( fact_netcustomers, fact_netcustomers[fnc_value] ),
            fact_netcustomers[dty_id_f] = 2,
            PARALLELPERIOD ( Datum[Datum], -1, MONTH )
        )
RETURN
    IF ( var_value < -1000000, BLANK (), var_value )

 

Any help would be much appreciated!

Best regards,

Trojan

1 ACCEPTED SOLUTION

Accepted Solutions
trojii Frequent Visitor
Frequent Visitor

Re: No Sum for Grand Total in a table using BLANK()

hi @v-piga-msft
Thank you for your answer and help!
I was able to solve it differently.
I changed the formula to be more flexible (no hardcoded value), plus, it showed the Grand Total, however, the wrong value. But the rows were right:

Netto-Neukunden Pre-Calc:=
VAR var_value =
    CALCULATE (
        SUMX ( fact_netcustomers; fact_netcustomers[fnc_value] );
        fact_netcustomers[dty_id_f] = 2
    )
        - CALCULATE (
            SUMX ( fact_netcustomers; fact_netcustomers[fnc_value] );
            fact_netcustomers[dty_id_f] = 2;
            PARALLELPERIOD ( Datum[Datum]; -1; MONTH )
        )
RETURN
    IF (
        var_value * -1
            = CALCULATE ( [Aktive Kunden]; PARALLELPERIOD ( Datum[Datum]; -1; MONTH ) );
        BLANK ();
        var_value
    )

Therefore, I calculated the sum over the measure with a different measure:

Netto-Neukunden:=
IF (
    ISBLANK ( [Netto-Neukunden Pre-Calc] );
    BLANK ();
    CALCULATE (
       [Netto-Neukunden Pre-Calc];
        FILTER ( Datum; [Netto-Neukunden Pre-Calc] <> BLANK () )
    )
)



Best regards,

Simon

2 REPLIES 2
Community Support Team
Community Support Team

Re: No Sum for Grand Total in a table using BLANK()

Hi @trojii,

 

I have tied to reproduce your issue and made a test demo, you could refer to it.

 

Untitled3.png

 

You could try to modify your formula like below.

 

Difference Blank :=
VAR var_value =
    CALCULATE (
        SUMX ( fact_netcustomers, fact_netcustomers[fnc_value] ),
        fact_netcustomers[dty_id_f] = 2
    )
        - CALCULATE (
            SUMX ( fact_netcustomers, fact_netcustomers[fnc_value] ),
            fact_netcustomers[dty_id_f] = 2,
            PARALLELPERIOD ( Datum[Datum], -1, MONTH )
        )
RETURN
    IF ( var_value < -1000000 && ISFILTERED ( [ColumnName] ), BLANK (), var_value )

If you need additional help please share some data sample and you can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
trojii Frequent Visitor
Frequent Visitor

Re: No Sum for Grand Total in a table using BLANK()

hi @v-piga-msft
Thank you for your answer and help!
I was able to solve it differently.
I changed the formula to be more flexible (no hardcoded value), plus, it showed the Grand Total, however, the wrong value. But the rows were right:

Netto-Neukunden Pre-Calc:=
VAR var_value =
    CALCULATE (
        SUMX ( fact_netcustomers; fact_netcustomers[fnc_value] );
        fact_netcustomers[dty_id_f] = 2
    )
        - CALCULATE (
            SUMX ( fact_netcustomers; fact_netcustomers[fnc_value] );
            fact_netcustomers[dty_id_f] = 2;
            PARALLELPERIOD ( Datum[Datum]; -1; MONTH )
        )
RETURN
    IF (
        var_value * -1
            = CALCULATE ( [Aktive Kunden]; PARALLELPERIOD ( Datum[Datum]; -1; MONTH ) );
        BLANK ();
        var_value
    )

Therefore, I calculated the sum over the measure with a different measure:

Netto-Neukunden:=
IF (
    ISBLANK ( [Netto-Neukunden Pre-Calc] );
    BLANK ();
    CALCULATE (
       [Netto-Neukunden Pre-Calc];
        FILTER ( Datum; [Netto-Neukunden Pre-Calc] <> BLANK () )
    )
)



Best regards,

Simon