Forum Discussion
DAX - SUMX does not return expected result
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 %:
2) measure to calculate the number of weeks before 80% has been reached:
3) tests to reach wanted 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
Hi lbraeckman ,
Try the measure below:
sumx(values(Movies[KMM_Name]), [OLD - Avg Week GBO > 80%])
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
4 Replies
- amitchandakSuper User
- lbraeckmanFrequent Visitor
Thanks for the suggestion, but this gives me 0 as result
- v-kelly-msftCommunity Support
Hi lbraeckman ,
Try the measure below:
sumx(values(Movies[KMM_Name]), [OLD - Avg Week GBO > 80%])
Best Regards,
KellyDid I answer your question? Mark my post as a solution!