Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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"))
Solved! Go to Solution.
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
Did I answer your question? Mark my post as a solution!
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)
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:
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
Did I answer your question? Mark my post as a solution!
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 )
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |