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

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

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

@ me in replies or I'll lose your thread!!!

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
Greg_Deckler
Super User IV
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.


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

@ me in replies or I'll lose your thread!!!

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!




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.

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


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

@ me in replies or I'll lose your thread!!!

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!




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

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

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

@ me in replies or I'll lose your thread!!!

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

brika
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.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

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.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

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

R2 (Green) 768 x 460px.png

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