Sales Coverage

alextf — Tue, 17 Mar 2020 14:38:57 GMT

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!

Re: Sales Coverage

Mariusz — Tue, 17 Mar 2020 17:13:37 GMT

Hi alextf

Please see the attached file with a solution included

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

topic Sales Coverage in DAX Commands and Tips

Sales Coverage

Re: Sales Coverage