REMOVEFILTERS basically working, but not without underlying data

[Basdo]

Hi everyone,

I've got a data model of this form:

Product	Month	Sales
1	May	100 $
1	June	200 $
1	July	300 $
2	May	100 $
2	July	200 $

Now I want to calculate the total Sales ignoring the 'Month' column so I built a measure:

TotalSales = CALCULATE( SUM(Table[Sales]), REMOVEFILTERS(Table[Month]) )

I want this measure to be shown even for months without underlying data, e.g. Product 2 in June. But when I visualize the TotalSales measure over months the outcome looks like thise

Product	May	June	July	Total
1	600 $	600 $	600 $	600 $
2	300 $		300 $	300 $

How can the measure TotalSals = 300 $ be also shown for Product 2 in June?

Thanks in advance for all support.

[Anonymous · Accepted solution]

Thanks for the reply from @GuillaumePower , please allow me to provide another insight: 
Hi  @Basdo ,

Here are the steps you can follow：

1. Create calculated table.

Table 2 =
DISTINCT('Table'[Product])

2. Connecting two tables.

3. Create measure.

Test =
var _table=
SUMMARIZE(ALL('Table'),[Product],"value",SUMX('Table',[Sales]))
return
SUMX(
    FILTER(
        _table,[Product]=MAX('Table 2'[Product])),[value])

4. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[SachinNandanwar · Accepted solution]

Its pretty easy.

Create a table using GENERATE

Define relationship across this table and your original table based on ProductId

And the use the generated table that I have named as MatrixTable in the Matrix visual

Regards,

Sachin Nandanwar

[Anonymous · Accepted solution]

Thanks for the reply from @GuillaumePower , please allow me to provide another insight: 
Hi  @Basdo ,

Here are the steps you can follow：

1. Create calculated table.

Table 2 =
DISTINCT('Table'[Product])

2. Connecting two tables.

3. Create measure.

Test =
var _table=
SUMMARIZE(ALL('Table'),[Product],"value",SUMX('Table',[Sales]))
return
SUMX(
    FILTER(
        _table,[Product]=MAX('Table 2'[Product])),[value])

4. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[GuillaumePower]

Hi, I would say that you've to remove the filter on the Product like this :

TotalSales = CALCULATE(

SUM(Table[Sales]), REMOVEFILTERS(Table[Month]), REMOVEFILTERS(Table[Product] )) so you 'll make disappear the row filter on the  product.

Try to do that and say if it works  as you want.

[Basdo]

Unfortunately this will only sum up over all Products and still keep the entry of Product 2 in June empty.
So the result looks like:

Product	May	June	July	Total
1	900 $	900 $	900 $	900 $
2	900 $	BLANK	900 $	900 $

This is unfortunately not what I want. Any other ideas? 🙂