cancel
Showing results for
Did you mean:
Highlighted
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?

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
Frequent Visitor

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

hi @v-piga-msft
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

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

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

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.
Frequent Visitor

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

hi @v-piga-msft
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