Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.