Running Total with FILTER on a Fact Table not working

[michaelshparber]

Hello friends,

***Edited***********

I think I've solved it.

This calculation is WRONG:

Running Sales Apples = CALCULATE([Running Sales],FILTER(Sales,Sales[Product]="Apples"))

And this one is right:

Running Sales Apples = CALCULATE([Running Sales],Sales[Product]="Apples")

So without FILTER() it is working.

Can anyone explain it, please?

********************

I have a simple Sales Fact Table and a Calendar table.

I have a Running Sales Amt which is working fine.

Running Sales = CALCULATE(Sales[Sales],FILTER(ALL('Calendar'),'Calendar'[Date]<=MAX('Calendar'[Date])))

When I add a Running Total measure that is filtered on a certain Product from the Fact table, like this:

Running Sales Apples = CALCULATE([Running Sales],filter(Sales,Sales[Product]="Apples"))

then I get wrong results - it doesn't sum up all the months...

In my real-world scenario I cannot create a "Product" dimension so I need to filter the fact table.

How do I do it?

Thanks!

Here is a sample file:

https://1drv.ms/u/s!AoP_9ampPIT72g8U1-BwSYzw9guI?e=ONyQY6

[v-yuta-msft]

@michaelshparber ,

They are different on dax engine. The similar thread in stackoverflow below and two blogs may help you understand the difference:

https://stackoverflow.com/questions/50506030/dax-calculate-function-with-and-without-filter

Blogs:

https://www.red-gate.com/simple-talk/sql/bi/using-the-filter-function-in-dax/

https://exceleratorbi.com.au/the-filter-function-in-dax/

Community Support Team _ Jimmy Tao

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

[michaelshparber]

@v-yuta-msft 

Thanks, I've looked at all of the articles.

Nowhere could I find the answer - why doesn't my Running Total work?

I did include ALL(Calendar) - and it is working until I do FILTER(Sales,...)

Please help to understand

Thank you

[michaelshparber]

I think I solved it.

This calculation is WRONG:

Running Sales Apples = CALCULATE([Running Sales],FILTER(Sales,Sales[Product]="Apples"))

And this one is right:

Running Sales Apples = CALCULATE([Running Sales],Sales[Product]="Apples")

So without FILTER() it is working.

Can anyone explain it, please?

[FrankAT]

Hi @michaelshparber ,

see figure:

Regards FrankAT

[michaelshparber]

Thanks @FrankAT 

I am looking for a solution to calculate for a specific product, such as Apples

[FrankAT]

Hi @michaelshparber ,

it's all insight:

Regards FrankAT

[michaelshparber]

Sorry, @FrankAT 

ALLEXCEPT doesn't fit because there are other fields in Fact table that can be drillable.

The solution is on the surface, why the plain FILTER is not working?

[Greg_Deckler]

Hmm, well, see attached and let me know what you think:

[michaelshparber]

@Greg_Deckler thanks, but SUMX is not the solution I am looking for.

Because of ALL(Sales) - I cannot drill down to details....

When there are other fields in the Sales table - your measure will return same amount for all of them.