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

Create a SUM measure that sums conditionally (based on a value in another column)

hi

i have two colums in by Bi

column 1 contains lot #, column 2 contains units

Lot                                units

L11551                         25

L11551                         25

L11524                         15

L11523                         34

L11523                         10

L11523                         12

i want add the sum the units based on the lot #

the results i am looking for are the following

 

L11551                         50       ( add the 2 rows that have the same lot # together)

L11524                         15

L11523                         56       (add the 3 rows that have the same lot # together)

 

any help would be apprecieated

 

Regards

5 REPLIES 5
Highlighted
Anonymous
Not applicable

Re: Create a SUM measure that sums conditionally (based on a value in another column)

Hi @Frankcf,

 

You can right click on Units in the Values field and select 'Sum' instead of 'Don't Summarize' which seems to be selected in your case.

Highlighted
Super User IV
Super User IV

Re: Create a SUM measure that sums conditionally (based on a value in another column)

You can do that with a Table visualization. If you put in your Lot column and then put in your units column with a SUM aggregation then you should have what you want.


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

Putting square pegs in round holes since 1972.

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
Continued Contributor
Continued Contributor

Re: Create a SUM measure that sums conditionally (based on a value in another column)

Hi @Frankcf,

 

In addiction what @Anonymous said, if u can't find this "SUM" option you have to check if this column is numeric, if no just change it.

 

Ricardo

Highlighted
Frequent Visitor

Re: Create a SUM measure that sums conditionally (based on a value in another column)

Hi

i am confused, but back and still trying to figure this out.

in the example below i want to sum the pcs column based on the number of time the same  internal lot is shown

in the example below i can see that internal lot No_ L1196480 is shown on 3 rows, the pcs for each row is 750.

the value i am looking for is 2250, for 750+750+750 = 2250

 

i cannot figure out how to count the # of times an internal lot is repeated and sum only that amout of pcs.

 

Capture.JPG

 

Highlighted
Super User IV
Super User IV

Re: Create a SUM measure that sums conditionally (based on a value in another column)

take a table visual, drop lot number and pcs in values, and drop down next to pcs and make sure aggregation  method is sum, if you don't see aggregation method, it means your pcs column is not a numeric field. if that is the case, select pcs column, go to modelling tab and change the data type to whole number.

 

 






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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors