Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi
I need to calculate the cumulative sum of inventory per item in order to make a waterfall chart to display the development of items in the warehouse. The image below shows my data. The problem arises because I have multiple posts with the same date, which becomes a problem when I try to make the waterfall chart.
How can I get past this?
Code to calculate the cumulated sum:
\Katrine
Solved! Go to Solution.
Hi @Rose__123
try this measure
Measure =
var _total=CALCULATE(SUM(Inventory_hist[Inventory_change]),FILTER(ALL(Inventory_hist),Inventory_hist[Data_change]<=MIN(Inventory_hist[Data_change])))
var _minNo=CALCULATE(MIN(Inventory_hist[No]),ALLEXCEPT(Inventory_hist,Inventory_hist[Data_change]))
return
IF(MIN(Inventory_hist[No])=_minNo,_total)
result
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Hi @Rose__123
try this measure
Measure =
var _total=CALCULATE(SUM(Inventory_hist[Inventory_change]),FILTER(ALL(Inventory_hist),Inventory_hist[Data_change]<=MIN(Inventory_hist[Data_change])))
var _minNo=CALCULATE(MIN(Inventory_hist[No]),ALLEXCEPT(Inventory_hist,Inventory_hist[Data_change]))
return
IF(MIN(Inventory_hist[No])=_minNo,_total)
result
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Hi @Rose__123
sample file attached below
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Waterfall charts do not work well if you have multiple data rows per x axis value. Try setting the aggregation for "cumul" to Average, or max.
what exactly is the problem? What should be the expected outcome?
@lbendlin, the problem arises e.g. on the 28. Juli, where I have five sales of 24, 48, 48, 48, and 60 items. When I calculate the cumulated sum by date (and item), then the number for all five rows will be the sum of those five sales (plus the sum of the earlier sales=276). And when I make my waterfall chart on behalf of those data, it gets the number 276 five times instead of one time.
I know that I could make a new table that removed the repeated lines, but I would like to avoid that.
Does it makes sense, or should I make an example?