cancel
Showing results for
Did you mean:
Frequent Visitor

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

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?

1 ACCEPTED SOLUTION
Super User IV
`MyDifference = AVERAGE([ItemCountExpected]) - SUM(OrderNumber[ItemRecieved])`

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

6 REPLIES 6
Super User IV

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.

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Frequent Visitor

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.

Super User IV

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

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Frequent Visitor

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

Super User IV
`MyDifference = AVERAGE([ItemCountExpected]) - SUM(OrderNumber[ItemRecieved])`

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Frequent Visitor

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.

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

#### Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors