Forum Discussion

ChumaAmako

Helper I

8 years ago

Solved

Sum with ALL Function filters

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.

Need Help

Ashish_Mathur

8 years ago

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

7 Replies

Interkoubess
Solution Sage
8 years ago
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
  Helper I
  8 years ago
  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
  - Interkoubess
    Solution Sage
    8 years ago
    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