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

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

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.
Highlighted
trojii 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