Reply
Highlighted
Regular Visitor
Posts: 42
Registered: ‎04-23-2016
Accepted Solution

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


Accepted Solutions
Super User
Posts: 2,146
Registered: ‎08-11-2015

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

[ Edited ]

@brett_walton

 

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

 

Hope this helps!

View solution in original post


All Replies
Super User
Posts: 2,146
Registered: ‎08-11-2015

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

[ Edited ]

@brett_walton

 

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

 

Hope this helps!

Regular Visitor
Posts: 42
Registered: ‎04-23-2016

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

It does , thanks for share with the beginner.

Frequent Visitor
Posts: 2
Registered: ‎08-04-2017

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

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

Frequent Visitor
Posts: 2
Registered: ‎08-04-2017

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

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

Regular Visitor
Posts: 30
Registered: ‎01-02-2018

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

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?

Visitor
Posts: 1
Registered: ‎03-16-2018

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

[ Edited ]

@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])
)

 

AMF Frequent Visitor
Frequent Visitor
Posts: 2
Registered: ‎08-15-2018

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

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!