Forum Discussion

mengna's avatar
mengna
Frequent Visitor
6 years ago

Decreasing Cumulative Totals with % Calculation

Hi All,

 

If I could get some help on a column/measure function for the "% sold" column below, that would be great. I currently have the first 4 columns and want to find just the % sold:

 

ItemTotal Qty PresoldLocation Qty PurchasedQty Remaining to sell% sold
Apple60A1050100%
Apple60B2030100%
Apple60C525100%
Apple60D40-1563%

 

I subtract the qty purchased from the starting qty to get the "qty remaining to sell" for each location, this is the decreasing cumulative qty.

 

Then, I want to find the % of that location sold. The formula for % sold is (Qty Purchased + Qty remaining to sell) / Qty Purchased, but defaulting to 100% or 0% if it's > or < 0.

 

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    mengna Not sure if I understood the final statement correctly. Please create a measure as per below and change as per your criteria

    Measure = 
VAR _numerator = SUMX('Table','Table'[Qty Purchased]+'Table'[Qty Remaining to sell])
VAR _denominator = SUMX('Table','Table'[Qty Purchased])
RETURN IF(DIVIDE(_numerator,_denominator,0)>1,1,0)

    .Current logic is if result is greater than 1 -->100% else  0% 

    • mengna's avatar
      mengna
      Frequent Visitor
      6 years ago

      I would also need to sum all the "Qty Purchased" by item type, this doesn't give me that filter.

       

      It would be 100% if > 1, 0% if < 0, otherwise it woud be the actual % calculation.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        mengna 

        Measure = 
VAR _numerator = SUMX('Table','Table'[Qty Purchased]+'Table'[Qty Remaining to sell])
VAR _denominator = SUMX('Table','Table'[Qty Purchased])
VAR _ratio = DIVIDE(_numerator,_denominator,0)
RETURN IF(_ratio>1,1,IF(AND(_ratio<1,_ratio>0),_ratio,0))

QuantityByItem = CALCULATE(SUM('Table'[Qty Purchased]),ALLEXCEPT('Table','Table'[Item]))