Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have two tables (attached you can find them),
They are connected with the key "date".
My goal is to sum up the budget for the year and divide it with the production days, so I get a new budget based on the production days.
With this formula I get the right figures per month:
Sum({$<[COPA.Material group 4_MVGR4_]-={'Q99'},plant={'CH30'}>}total[BU quantity])
/
sum(total[production days])
*
sum([production days])
But if I want to use filter like material or client the figures are wrong. You have any ideas to solve my problem?
Thanks a lot,
Lisa
The data model doesn't match your desired logic. When you drill down on something like client it's reducing the data set to only the first of the month and the other days in the month with their production days are ignored.
One approach could be to use aggr and {1} as your set analysis in expressions, but this would be fiddly and unique per chart.
A simpler approach would be to rollup the calendar to being one record per month with the number of productions days being aggregated.
I found a solution: add e.g. month= to the measure.