Hi - I'm a little confused with your terminology - different between the narrative and the workbook but if your products = reference then see below and attached
countd = if [Running Sales]>0 then countd([Reference]) end
If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.
thanks for the help. Did you attach a workbook? I don't manage to replicate your results just with the formula proposed.
Sorry for the mismatch between narrative and workbook. You're absolutely right, so here is the narrative corrected:
I analyse monthly sales of Reference(s).
Each Reference has an initial stock.
Reference(s) belong to Categories
I calculate the Running Stock = ATTR([Initial Stock])-[Running Sales]
Running Sales being the RUNNING_SUM(SUM([QTY]))
Then, for each Category, I want to calculate the monthly CNTD of References that have a stock above Minimum stock to count as Offer.
That's where the problem is: calculated fields or LODs refuses to do double aggregation. So I cannot see the totals per Category.
thanks for the help.
I identified a bug and wonder how to calculate the % total:
1.There seems to be an bug: displayed values are not correct (Minimum stock to count as Offer threshold seems not to be respected) - see screenshot below.
2. The final step is to calculate the Percent of Total. So here it would be like:
1st column: 33% and 66%
2nd column: 50%, 25% and 25%
3rd column: 100%
The solution you propose works on the Sales. Instead, what I want to show is the CNTD(References that have a stock >= Minimum stock to count as offer).
As you can see in screenshot below, your solution is taking into account References that have less stock than the minimum authorized.
I wanted to insert the workbook updated with yours and Zhouyi's proposed solutions but doesn't seem to be possible in this reply...
Thanks for your feedback.
I tried to understand what you want to achieve and update my solution based on my understanding. I highlighted the calculation fields I added or modified below
I attached the workbook for your reference.
Here is a screen shot of end result I have.
Hope this could help
LG.twbx 46.3 KB