Hello,
How are you?
I was wondering if someone could help me with this measure. We call it COVERAGE.
Coverage means the sales of a portfolio of products that i need to sell in one particular client. If i sold at least 1 product and this product is at the list of "must be sold" on that particular client, i need Power BI to count 1. If the sell is not listed as "must be sold", I need it to count 0.
Besides, i need to sum all of those sales, but with distinctcount.
The thing is, I'm using SUMX to sum products sold, since I need the total "coverage" for each month, for every client in each month. I need to find a way to limit the way SUMX sums, somehow filtering in the same manner distinctcount does. The problem I'm having right now is that SUMX sums every available piece of data, hence duplicating the amount of data I need (see example below for more clarity).
Here´s an example:
Date | Client | Product | Sales Unit | Must be? |
1/1/2020 | Client 1 | X | 6 | YES |
1/1/2020 | Client 1 | Y | 5 | YES |
1/1/2020 | Client 1 | Z | 10 | NO |
5/1/2020 | Client 1 | X | 8 | YES |
5/1/2020 | Client 1 | Y | 3 | YES |
So, as we can see, the product "Z" is not in the "must be" list, but products "X" and "Y" are in it.
Considering this list of products of "must be" in client 1:
Clients | Product | YES/NO |
Client 1 | X | YES |
Client 1 | Y | YES |
Client 1 | W | YES |
Client 1 | Z | NO |
So, the correct result of coverage for client 1 in this month is 66% (because it has 2 sales of the 3 products of the"must be" list.
The DAX i was using was:
COVERAGE=
SUMX(
Sales;CALCULATE(
DISTINCTCOUNT(Sales[Products]);Sales[Must be?]="YES";Sales[Sales Unit]>0))
/
CALCULATE(
COUNT(Must be List[Products]);Must be List[YES/NO]="YES"))
The result that is bringing to me in this case is 133% because its adding the different sales date. But the true result that i need to bring to me is 66%.
PD: If i use the calculate measure only, without the sumx, it brings to me 66%, but i need the sumx because i need to evaluate this measure in a higher level of hierarchy. Like this:
Clients | % Coverage | Sold within Must Be list |
Client 1 | 66% | 2 |
Client 2 | 33% | 1 |
Client 3 | 100% | 3 |
TOTAL | 66% | 6 |
Thanks in advance!
