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:   Ite...
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]))