DAX - SUMX does not return expected result

[lbraeckman]

Hi All,
We need to calculate the average number of weeks that are passed before 80% of the sales are reached. So first we need a calculation to get this measure per product (KMM_Name), then we need the average of that. We want this per product, but those have subproducts as well, these do not to be taken into account. The source is a tabular cube, so we are not able to create additional tables/columns. The tests we did are the following:
1) measure to calculate the cumulative sales %: 

Arc of Life GBO = [**bleep** GBO]/CALCULATE([TS Gbo (Leg curr)], ALL('Session Properties'[FilmWeek]),ALL('Date Country Properties'[Dat_CouC_sYWeek]))

2) measure to calculate the number of weeks before 80% has been reached:

Is Filmweek GBO < 80% =

IF([TS Gbo (Leg curr)] <> BLANK(), IF([Arc of Life GBO] < 0.8 , DISTINCTCOUNT('Session Properties'[FilmWeek]),BLANK()),BLANK())
3) tests to reach wanted result:

OLD - Avg Week GBO > 80% =

VAR mult_weeks = SUMX(FILTER(values('Session Properties'[FilmWeek]), [TS Gbo (Leg curr)]>0), [Is Filmweek GBO < 80%])

VAR min_filmweek = FIRSTNONBLANK('Session Properties'[FilmWeek], [Arc of Life GBO])

var cor_reached_week1 = CALCULATE([Arc of Life GBO], Filter(all('Session Properties'[FilmWeek]), 'Session Properties'[FilmWeek] = min_filmweek))

VAR count_ = CALCULATE(DISTINCTCOUNT(Movies[KMM_Name]), filter(Movies, [TS Gbo (Leg curr)] >0))

VAR result = SUMX(FILTER(SUMMARIZE(Movies, Movies[KMM_Name]), [TS Gbo (Leg curr)]>0), if(mult_weeks <> BLANK(), mult_weeks+1, if(cor_reached_week1 >0.8, 1, blank())))

VAR hasonev = if(HASONEVALUE(Movies[KMM_Name]),result,0)

VAR cor = if(HASONEVALUE(Movies[KMM_Name])=TRUE(), hasonev, sumx(Movies, hasonev))

RETURN result
mult_weeks calculates the number of weeks sales are ongoing before 80% of sales is reached, but only works for products (KMM_Name) that do not reach this in week 1. Therefore the next 2 variables are created: min_filmweek calculates the first week where the sales are not empty. cor_reached_week1 calculates the corresponding arc of life (%sales) of that first week. Result makes the combination of the products where multiple weeks were needed (there we add 1 as the measure [Is FilmWeek GBO <80%] does not take the week of 80% into account) and the products where the goal was reached in the first week. This gives a correct result per product (KMM_Name), but not in total. The hasonevalue and cor variables are measures that tested some fixes we found on the internet, but they do not give the correct result. The count_ variable is not used yet, but our expectation would be that in the end result/count_ would give us the average number of weeks needed to reach the 80% sales. 

In the above picture we would expect 33. With 11 movies this would result in an average of 3. The 3 is our wanted end result.

I hope the explanation is clear enough to give us some tips.
Kind regards,

Lise

[amitchandak]

@lbraeckman , change this var and check

VAR cor = sumx(values(Movies[KMM_Name]), hasonev)

[lbraeckman]

Thanks for the suggestion, but this gives me 0 as result

[v-kelly-msft · Accepted solution]

Hi  @lbraeckman ,

Try the measure below:

 sumx(values(Movies[KMM_Name]), [OLD - Avg Week GBO > 80%])

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[lbraeckman]

Super, this solves it, thank you!