Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Add category to cumulative sum

Hi,

Appreciate if i could get some help for my issue. I tried to add category for running sum calculation result. I created a measure to calculate the cumulative/running sum and based on running sum result i would to add a column to identify if it "into reserve" or "Stock out". I tried to use measure and calculated column however still i cant get the correct result.

 

Running Sum Dax:

Quantity Total Stock = VAR CurrDevYear = MAX('03 Calander Table'[Date])

RETURN

CALCULATE(

SUM('Inventory Trend'[Quantity Total Stock]),

FILTER(ALL('Inventory Trend'[Calendar Day]), 'Inventory Trend'[Calendar Day] <= CurrDevYear)

)

 

Based on the running sum measure, i tried to create measure and calculated field as per below but still could not get the correct result.

 

  1. Measure : 

Stock Status = IF('01 Basic Measures table'[Quantity Total Stock]= 0 && '01 Basic Measures table'[Safety Stock] > 0,"Stock Out",IF('01 Basic Measures table'[Quantity Total Stock]<'01 Basic Measures table'[Safety Stock],"Into Reserve","OK"))

 

Result

it show the result as what i needed but by using measure i cant use it in slicer or filter.

 

2. Calcultated column.

Stock Out =
SWITCH(TRUE(),
[Quantity Total Stock] = 0,"Stock Out",
[Quantity Total Stock] > 0,"Into Reserve",
"OK"
)
 
Result:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Data became duplicate in the day that have stock value. I'm not sure why.

 

I'm hoping to get the same result if i'm using measure in calculated column. 

 

Appreciate your help.

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi Anonymous 

    As ryan_mayu suggested, you could add a calculated column like:

    column=var addsum=addcolumns(....) return if(..)

    But calculated column doesn't change when you change date from the slicer.

    you could use the correct measure you have, then create a measure like

    measure1=selectedvalue(slicer[slicer value])

measure2=if([your measure]=[measure1],1,0)

    slicer is a table you can create with values: "into reserve" , "Stock out","OK",

    then add [measure2] to the visual level filter or page/report visual filter, and apply it show values when it equals 1.

    It would act as a slicer.

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.