cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
Super User IX
Super User IX

Re: How to create a measure to subtract a column that is summed from a column that should not be sum

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

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

Not link spamming! If I posted a link in reply it is because I did assume you know how to search the web but thought it was the fastest way to fix your issue.

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

6 REPLIES 6
Highlighted
Super User IX
Super User IX

Re: How to create a measure to subtract a column that is summed from a column that should not be sum

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.


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

Not link spamming! If I posted a link in reply it is because I did assume you know how to search the web but thought it was the fastest way to fix your issue.

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Frequent Visitor

Re: How to create a measure to subtract a column that is summed from a column that should not be sum

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.

Highlighted
Super User IX
Super User IX

Re: How to create a measure to subtract a column that is summed from a column that should not be sum

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


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

Not link spamming! If I posted a link in reply it is because I did assume you know how to search the web but thought it was the fastest way to fix your issue.

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Frequent Visitor

Re: How to create a measure to subtract a column that is summed from a column that should not be sum

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

Highlighted
Frequent Visitor

Re: How to create a measure to subtract a column that is summed from a column that should not be sum

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.

Highlighted
Super User IX
Super User IX

Re: How to create a measure to subtract a column that is summed from a column that should not be sum

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

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

Not link spamming! If I posted a link in reply it is because I did assume you know how to search the web but thought it was the fastest way to fix your issue.

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Kudoed Authors