Sum with ALL Function filters

[ChumaAmako]

Hi guys,

I have a stock count table for different products in different outlets.

Like this:

Outlet ID SKU ID Total Stock Count
1001 Apples 12
1001 Bananas 20
1001 Carrots 0
1002 Apples 0
1002 Bananas 13
1002 Carrots 25
1003 Apples 9
1003 Bananas 0
1003 Carrots 35

I need to show the total stock quantity of all the outlets in which a product is available like this

SKU ID Total StockCount of Handlers
Apples 76
Bananas 70
Carrots 82

I have tried the following formula without succuess:

Total Stockcount of Handlers:=CALCULATE(SUM(StockCount[Total Stock Count]), ALL(StockCount[SKU ID]),StockCount[Total Stock Count]>0)

Can anyone please spot what I am doing wrong?

Thank you.

[Interkoubess]

Hi @ChumaAmako,

Could you please with your sample data tell us what are your expected outcomes?

Because with a simple Table I have this picture ( without any measure for the moment) below:

Ninter

[ChumaAmako]

Hi @Interkoubess thanks for your response.

Please see below the sample of the data set I am working with

And this is the expected outcome

The aim is to create a measure that sums up the total stock count of all outlets that have the SKU in stock.

Hope this helps to clarify

You can also view/edit the sample data here (google drive): https://drive.google.com/file/d/0Byxc8IzPhZ5-NE5RVllkRlhiOTQ/view?usp=sharing

Thank You

[Ashish_Mathur · Accepted solution]

Hi,

Here is the calculated field formula i wrote

=CALCULATE(SUM([Total stock count]),ALL(Data[SKU ID]),FILTER(SUMMARIZE(VALUES(Data[Outlet ID]),[Outlet ID],"ABCD",SUM(Data[Total stock count])),[ABCD]>0))

[ChumaAmako]

Hi @Ashish_Mathur this works even better and is more system resource efficient, because you used a measure and not a calculated column.

Thanks so much for your help as always.

God Bless you.

Regards,

Chuma.

[Ashish_Mathur]

You are welcome.

[Interkoubess]

Hi @ChumaAmako,

I created a column with the formula below ( my table is called FinalSan then I created a Table:

TestColumn2 =
IF (
    FinalSan[TotalStock Count] = 0,
    0,
    CALCULATE (
        SUMX ( FinalSan, FinalSan[TotalStock Count] ),
        ALLEXCEPT ( FinalSan, FinalSan[Outlet ID] )
    )
)

Please let us knw if you have any observations.

Ninter

[ChumaAmako]

Hi @Interkoubess this works fine for me.

Thank you.

Regards,

Chuma.