Forum Discussion
How To Sum Differences of Cumulative Values/Measures?
I need a little help trying to figure out how to (What I'm going to call) summing the difference of a cumulative value/measure.
I have the following model where I am trying to combine Forecated values with actual deliveries.
Based on this Model I display the data in the following way.
Based on the data above the end goal is to come up with a calculation to provide the % shipped on-time which would be:
(57-4)/57 = 92.98%
I'm struggle with creating a calculation/measure that will provide the result that I'm looking for. Part of the issue (For me) is that this difference is based on 2 cummulative meaures and I can't seem to figure out the correct calculation or formulms to use.
I'm simply looking for a way to issolate that Qty of -4 from Week 4.
Here is the DAX for all 6 columns above
Diff 2 is ultimitally giving me the correct answer (on a row by row basis) but I need to create a column that will sum/total all the negative values in order to perform the following calculation:
(57-4)/57 = 92.98%
2 Replies
- v-chuncz-msftCommunity Support
- AnonymousNot applicable
Thanks! That did the trick. I actually had to use SummarizeColumns instead of Summarize since I had data coming from multiple tables, however, I have the answer. Its funny, I had tried SUMX, Summarize, and SummarizeColumns seperatly but never thought of using SUMX with the Summarize functions.
Total Diff = SUMX( SUMMARIZECOLUMNS( Lot[Lot Desc], 'Date'[Year], 'Date'[Week Number], "Forecast Qty", [Cumm Forecast Qty], "Ship Qty", [Cumm Shipped Qty], "Diff", [Diff 2] ), [Diff 2] )