Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Dear Forum,
it would be great if anyone could bring me on the right track with the following issue.
I have a table similar to this table 'Sales':
Deal ID | Deal Closed | Deal Won | Reseller |
001 | FALSE | FALSE | A |
002 | TRUE | FALSE | B |
002 | TRUE | FALSE | B |
003 | FALSE | FALSE | A |
004 | TRUE | FALSE | B |
005 | TRUE | TRUE | A |
005 | TRUE | TRUE | A |
006 | TRUE | TRUE | B |
Now, I want to know, how many deals each Reseller has lost.
A deal is lost, when the following conditions apply:
The twist here: Some deals (here 'Deal ID' 002 and 005) are double in the table, but each 'Deal ID' should only be counted once!
The result should look like:
Attempt to solve this challenge so far:
In case, each [Deal ID] would only occur once in the table, then the following Measure works:
COUNT_Reseller_Lost =
CALCULATE (
COUNT ( 'Sales'[Reseller] ),
FILTER (
'Sales',
'Sales'[Deal Closed] = TRUE()
&& 'Sales'[Deal Won] = FALSE()
)
)
But how to filter out the deals with double entry?
For only counting each deal per reseller once without the conditions 'Deals Closed' and 'Deals Won', I've found this measure, which might work:
Distinct_Count_Deal_ID =
CALCULATE(
DISTINCTCOUNT('Sales'[Deal ID]),
ALLEXCEPT('Sales','Sales'[Reseller])
)
But how to combine both?
Thank you very much!
Mister_T
Solved! Go to Solution.
One way to combine them:
COUNT_Reseller_Lost =
CALCULATE (
DISTINCTCOUNT ( 'Sales'[Deal ID] ),
FILTER (
ALLEXCEPT ( 'Sales', 'Sales'[Reseller] ),
'Sales'[Deal Closed] = TRUE ()
&& 'Sales'[Deal Won] = FALSE ()
)
)
Edit: DISTINCTCOUNT ( 'Sales'[Reseller] ), --> DISTINCTCOUNT ( 'Sales'[Deal ID] ),
Wow, that was quick! Thanks a lot @AlexisOlson !
I assume, in your measure was a small typo and it is supposed to be "DISTINCTCOUNT ( 'Sales'[Deal ID] )" instead of "DISTINCTCOUNT ( 'Sales'[Reseller] )".
COUNT_Reseller_Lost =
CALCULATE (
DISTINCTCOUNT ( 'Sales'[Deal ID] ),
FILTER (
ALLEXCEPT ( 'Sales', 'Sales'[Reseller] ),
'Sales'[Deal Closed] = TRUE ()
&& 'Sales'[Deal Won] = FALSE ()
)
)
Tested on the dataset given, it seems to work! 🙂
You are correct. I missed changing that part when I copied and pasted.
One way to combine them:
COUNT_Reseller_Lost =
CALCULATE (
DISTINCTCOUNT ( 'Sales'[Deal ID] ),
FILTER (
ALLEXCEPT ( 'Sales', 'Sales'[Reseller] ),
'Sales'[Deal Closed] = TRUE ()
&& 'Sales'[Deal Won] = FALSE ()
)
)
Edit: DISTINCTCOUNT ( 'Sales'[Reseller] ), --> DISTINCTCOUNT ( 'Sales'[Deal ID] ),
Wow, that was quick! Thanks a lot @AlexisOlson !
I assume, in your measure was a small typo and it is supposed to be "DISTINCTCOUNT ( 'Sales'[Deal ID] )" instead of "DISTINCTCOUNT ( 'Sales'[Reseller] )".
COUNT_Reseller_Lost =
CALCULATE (
DISTINCTCOUNT ( 'Sales'[Deal ID] ),
FILTER (
ALLEXCEPT ( 'Sales', 'Sales'[Reseller] ),
'Sales'[Deal Closed] = TRUE ()
&& 'Sales'[Deal Won] = FALSE ()
)
)
Tested on the dataset given, it seems to work! 🙂
You are correct. I missed changing that part when I copied and pasted.
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |