cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Sum of a measure value

Hi everyone,

I have a Table like below that that states whether a quote ended up being a sale which means Won versus Loss. The WinLoss column is a calculate column that I made. If I filter by product PC for example this is what the table visual looks like. In reality this product was "Won" 8 times, but my measure, written below, says 11 because some of the customers bought the same product and quantity.

I am trying to calculate the win rate for only 1 specific scenario. The win rate comes out to be 11/18 when the value I am looking for is 8/18.

Is there a way to "fix" my measure and only count each distinct scenario it was won rather than if the customer was quoted on a product and quantity X amount of times and "Won" it that many times?

 Cust Product WinLoss Apple PC Lost Paypal PC Won Samsung PC Won Samsung PC Lost HP PC Won Amazon PC Won Amazon PC Lost Disney PC Lost Intel PC Won Netflix PC Won Oracle PC Lost Nippon PC Lost IBM PC Lost Softbank PC Lost Cisco PC Lost Sap PC Won Verizon PC Won Dell PC Lost

The measure I currently have is written below and returns "11" instead of the value "8" that I wish it would be. This is because the same customer was quoted on the same product and quantity and the sale was made.

It may be rather confusing, but thank you!

``Total Won = COUNTROWS(FILTER('Sales&Quote Duplicate','Sales&Quote Duplicate'[WinLoss] = "Won"))​``

1 ACCEPTED SOLUTION
Community Support

Hi  @Anonymous ,

I duplicate your original data for example:

Create a measure as below:

``````Measure =
var _tab=SUMMARIZE('Sales&Quote Duplicate','Sales&Quote Duplicate'[Cust],'Sales&Quote Duplicate'[Product],"win",IF(MAX('Sales&Quote Duplicate'[WinLoss])="Won",1,BLANK()))
Return
SUMX(_tab,[win])``````

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

4 REPLIES 4
Community Champion

Hi @Anonymous ,

with your measure I get the expected results:

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

Anonymous
Not applicable

The table I provided is just a visual and not the actual data sheet. "Behind the scenes" the reason I get 11 for that specific product is because in the data sheet the same customer, product, and quantity was sold twice or more times.

For example, Netflix was quoted on a PC for quantity 1, but bought it twice, thus having a two "won".

I am trying to get it to say if a customer was quoted on a product for X quantity. No matter how many times that customer bought that product for that specific X quantity it counts 1.

Community Support

Hi  @Anonymous ,

I duplicate your original data for example:

Create a measure as below:

``````Measure =
var _tab=SUMMARIZE('Sales&Quote Duplicate','Sales&Quote Duplicate'[Cust],'Sales&Quote Duplicate'[Product],"win",IF(MAX('Sales&Quote Duplicate'[WinLoss])="Won",1,BLANK()))
Return
SUMX(_tab,[win])``````

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Resolver II

Maybe try using HASONEFILTER function, something like below:

Total Won =
VAR count_won =COUNTROWS(FILTER('Sales&Quote Duplicate','Sales&Quote Duplicate'[WinLoss] = "Won"))

IF(HASONEFILTER('Sales&Quote Duplicate'[Cust]),
COUNTROWS(FILTER('Sales&Quote Duplicate','Sales&Quote Duplicate'[WinLoss] = "Won"))​

,count_won )

Announcements

#### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

#### What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.