cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
brett_walton
Helper II
Helper II

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

I have the following data in the same source

 

Status         Qty

P                   10

P                   20

M                  50

M                  30

C                  25

 

I want to create a messure that SUMs the qty's but only for status P. I know how to do it with a SUM and a filter on a viz but I was hoping to create a meassure as this type of calc will be used in many cards and other queries. '

 

Thanks

1 ACCEPTED SOLUTION
Sean
Community Champion
Community Champion

@brett_walton

 

Measure = CALCULATE(SUM(TableName[Qty]), TableName[Status]="P")

 

Hope this helps!

View solution in original post

8 REPLIES 8
nehabarnwal
Regular Visitor

What if I want to sum for more than one status? How do I do?

nehabarnwal
Regular Visitor

What if I wish to get the sum of quantity for P as well M status but not C? How should I do it?

Sean
Community Champion
Community Champion

@brett_walton

 

Measure = CALCULATE(SUM(TableName[Qty]), TableName[Status]="P")

 

Hope this helps!

Anonymous
Not applicable

i can't make this "Create a SUM measure that sums conditionally (based on a value in another column)" in if condition. could anyone Know the solution for this? Please share the syntax.


I have a simmilare issues 

 

what i would liek to do is show all values P, M, C in a chart and show the sum of all Qty assosated to them.

 

can this be done without having to create a Mesure for every Status?

@nehabarnwal @Colinu @brett_walton @Sean

 

You can try the following to Group By elements that are in your rows:

 

e.g. 

MyMeasureByGroup = 
CALCULATE(
SUM('MYTABLE'[COLUMN1]),
GROUPBY('MYTABLE', [COLUMN2])
)

 

Hi,

 

I have the following situation.

How could I sum the figures in column 2 by taking only one amount per each value in column 1?

By example, for value 3, I should have an average of the amounts displayed in column 2 and add it to the average amount for value 2 and so on.

 

 

Column 1 Column 2 
3          10,00 €
3          10,00 €
3          50,00 €
2          10,00 €
1            2,00 €
2            3,00 €
3          45,00 €

 

Thank you!

It does , thanks for share with the beginner.

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors