The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
Hi All,
I am struggling to work out what I think should be some fairly straightforward DAX and can't find anything in the forum. Apologies if I have missed another post.
I have a client and product columns. What I am trying to achieve is to calculate what 'client coverage' each product has. Client coverage is defined by the percentage of clients that have a product and is between 0% and 100%. What is throwing a spanner in doing a COUNT(Products)/COUNT DISTINCT(Clients) is that some clients have some products more than once; the way I want my calculation to work is to be binary: A client has it or doesn't. I am not interested in how many times they have bought it.
Example data:
Client | Product |
A | 1 |
A | 2 |
A | 3 |
B | 1 |
B | 1 |
B | 2 |
C | 1 |
In this instance, all clients have product 1, but Client B has it twice. Therefore coverage should be 100% not 133% if I did the formula above. Expected calculated output:
Product | Client Coverage |
1 | 100% |
2 | 67% |
3 | 33.33% |
Any help would be greatly appreciated.
Thank you!
[Client Coverage] =
// If more than 1 product is visible
// in the current context it returns
// the minimum coverage among all the
// visible products. So, if 1 product
// is visible, it agrees with your
// definition. If you want this measure
// to return BLANK if many products are
// visible/selected, put the body in
// IF( HASONEVALUE( T[Product] ),...
var AllClientCount =
CALCULATE(
DISTINCTCOUNT( T[Client] ),
ALL( T )
)
var Result =
DIVIDE(
MINX(
DISTINCT( T[Product] ),
CALCULATE( DISTINCTCOUNT( T[Client] ) )
),
AllClientCount
)
return
Result
Good morning Daxer,
Thanks for this! I noticed you replied to my colleague's post with the same query - I had a few questions on your response, hoping you can help:
- You have declared AllClientCount but I don't see it used anywhere else in the measure, is this intentional?
- The variable VisibleClientCount isn't declared so will error when trying to run this.
- When you say 'put the body in', where do you mean I should include this in the measure?
Thanks v much in advance for your help!
Of course there's a mistake in the code but it's obvious: change the name of the variable that does not exist to the one that does. The above formula is one way to solve the problem. Another one is in the other post you mention.
"Put the body in..." means that the whole body of the measure should be wrapped in "IF(..."
User | Count |
---|---|
26 | |
10 | |
8 | |
6 | |
6 |
User | Count |
---|---|
31 | |
11 | |
10 | |
10 | |
9 |