How to create a measure to subtract a column that is summed from a column that should not be summed.

[brika]

I have a column that is a whole number that I have set to to "Don't Summarize" in the visualization and I have another column that is a sum of the value that is in multiple rows. I need to create a measure that is the difference between these two columns. However, the formula for the column that should not be summed fails if I remove an aggregation against it.

Here's what I'm trying to get to:

CountDifference = (Table1[ItemCount]) - SUM(Table1[Item2Count])

However, this is failiing. How do I write this formula to skip any summarization/aggregation for the first column?

[Greg_Deckler]

Can you use SUM in the measure anyway? The SUM should take it's context into account and if you have the first column to not summarize then it should honor that context. I'd have to see some data and how you are using it in your visual.

[brika]

I tried using SUM and COUNT, they both handle the number incorrectly. This first column is repeated across each of the rows that are being summed up in the 2nd column. So the outcome with either of those options is incorrect.

I'm really hoping there's an equivalent to the visualization's "Don't Summarize" option that I can use in the measure formula.

[Greg_Deckler]

I'd have to see the data behind this to understand what is going wrong here. 

[brika]

Here's a simulation. For background, my table has a row for every item and if it has been received or not. I'm pulling in purchase order data that tells me how many items I'm expecting in that Order. I'm trying to do this measurement because our data is missing item rows in some of the Orders and I'm want to quickly identify the descrepancy to resolve the issue.

OrderNumber, ItemCountExpected, ItemRecieved

123, 5, 1

123, 5, 1

123, 5, 1

113, 3, 1

113, 3, 1

113, 3, 1

In the matrix or table visualization in the report, I want to see the following:

OrderNumber, ItemCountExpected (Not Summarized), ItemCountReceived (Summarized), CountDifference (new measure)

123, 5, 3, 2

113, 3, 3, 0

[Greg_Deckler · Accepted solution]

MyDifference = AVERAGE([ItemCountExpected]) - SUM(OrderNumber[ItemRecieved])

[brika]

While I'd still like to see if this is possible using a Measure, I solved my problem a different way. In the queries I referenced my table and created a new table. I used Group By in the new table and created my desired aggregrate view. I then added a custom column that let me subtract the 2nd count column from the first count column and I know have what I'm looking for. I'll use this new table to create the visualizations I need in the dashboard/report.

However, please let me know if there's a way to do this in a measure in a way that doesn't require a sum/count on one of the values.