cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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

PaypalPCWon
SamsungPCWon
SamsungPCLost
HPPCWon
AmazonPCWon
AmazonPCLost
DisneyPCLost
IntelPCWon
NetflixPCWon
OraclePCLost
NipponPCLost
IBMPCLost
SoftbankPCLost
CiscoPCLost
SapPCWon
VerizonPCWon
DellPCLost

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

Hi  @Anonymous ,

 

I duplicate your original data for example:

vkellymsft_1-1626424314807.png

 

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:

vkellymsft_0-1626424258327.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

 

View solution in original post

4 REPLIES 4
FrankAT
Community Champion
Community Champion

Hi @Anonymous ,

with your measure I get the expected results:

 

13-07-_2021_23-19-35.png

 

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.

Hi  @Anonymous ,

 

I duplicate your original data for example:

vkellymsft_1-1626424314807.png

 

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:

vkellymsft_0-1626424258327.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

 

alexa_0028
Resolver II
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 )

Helpful resources

Announcements
Microsoft Build 768x460.png

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_carousel_with_text (1).png

Charticulator Design Challenge

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

May UG Leader Call Carousel 768x460.png

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.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!