Decreasing Cumulative Totals with % Calculation

[mengna]

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:

Item	Total Qty Presold	Location	Qty Purchased	Qty Remaining to sell	% sold
Apple	60	A	10	50	100%
Apple	60	B	20	30	100%
Apple	60	C	5	25	100%
Apple	60	D	40	-15	63%

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.

[Anonymous]

@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]

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]

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