cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Johnlogistic Regular Visitor
Regular Visitor

DAX subtraction formula without summing up when there are multiple entries of the same category

Hello everyone,

I have a formula which does a basic subtraction:

MAX_LOAD_LENGTH minus LDM =
SUM('Truck_Info'[MAX_LOAD_LENGTH]) - SUM('Truck_Info'[LDM])
 
the max load length is the capacity and ldm is the load  with one row being one truck/drive. Now there can be multiple routes a day for the same truck when its a short route, meaning the truck 5 could exist 3 times. In this case the formula sums the values of those 3 entries and subtracts it. I don't want this even if it exists multiple times I still want it to only do it for that specific truck. I think i need to convert this to a calculated column to avoid the summing up when there are multiple entries of the same category.
example of data:
Truck | MAX_LOAD_LENGTH | LDM
5           10                                5
5           10                                3
5           10                                2


In the current situation the result would be:
Truck   |  FREE LDM
5              20

I want it to be like this
Truck  |   FREE LDM
5                5
5                7
5                8
1 ACCEPTED SOLUTION

Accepted Solutions
Nathaniel_C Super Contributor
Super Contributor

Re: DAX subtraction formula without summing up when there are multiple entries of the same category

Hi @Johnlogistic ,
Your issue may be as easy as checking Don't Summarize, by clicking on the fields that you are bringing in. See pics. I also did another measure that you might use, although I think yours is fine. Measures are superior to doing a calculated column. 
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
Dontsum1.PNG

 

MAX_LOAD_LENGTH minus LDM 1 = 
MAX(mytable[MAX_LOAD_LENGTH]) - Max(mytable[LDM])

Dontsum.PNG

 

 

View solution in original post

1 REPLY 1
Nathaniel_C Super Contributor
Super Contributor

Re: DAX subtraction formula without summing up when there are multiple entries of the same category

Hi @Johnlogistic ,
Your issue may be as easy as checking Don't Summarize, by clicking on the fields that you are bringing in. See pics. I also did another measure that you might use, although I think yours is fine. Measures are superior to doing a calculated column. 
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
Dontsum1.PNG

 

MAX_LOAD_LENGTH minus LDM 1 = 
MAX(mytable[MAX_LOAD_LENGTH]) - Max(mytable[LDM])

Dontsum.PNG

 

 

View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)