Forum Discussion
Sum with ALL Function filters
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))
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
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
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
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))
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.
