How To Sum Differences of Cumulative Values/Measures?

[Anonymous]

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%

[v-chuncz-msft · Accepted solution]

@Anonymous,

Try using SUMX Function to add an additonal measure.

https://community.powerbi.com/t5/Desktop/Sum-of-values-in-a-measure-with-divide-measure/m-p/296768#M130947

[Anonymous]

@v-chuncz-msft, 

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]
    )