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

[Anonymous]

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?

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

[v-piga-msft]

Hi @Anonymous,

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

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

[Anonymous · Accepted solution]

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