Looking for a DAX measure to bring back not carrying main SKU but Alt SKUs

[brockry1]

I am looking for a measure to include in my report to show how many clinics are carrying the "Alt SKUs" but not the main SKU.  We are looking at penetration for main SKUs then these Alt SKUs but not the main SKU.  Below is the formula for the clinics carrying:

And this is my DAX measure for clinics not carrying:

The next layer is to figure out the clincis not carrying the main SKU but carrying the ALT SKUs.  I created a conditional column in my power query for the Alt SKUs tied to the main SKU.

Below is an example of the main SKUs then the Alt listed below them. Alt SKUs are below the solid black line. 

I had to manually calculate this in excel and it was not ideal so now building out a report to automate it.  Below is an example of the excel that i'm trying to get in here:

Any help would be appreciated. 

[v-pgoloju]

Hi @brockry1.,

Great to hear that it's working as expected on your end! Could you please Post the solution and accept as solution? It would be really helpful for others in the community who might be facing similar issues and can address them quickly. Also, I would suggest accepting your approach as the solution so that it can benefit others as well.

Thank you & regards,
Prasanna Kumar

[v-pgoloju]

Hi @brockry1,

We wanted to kindly check in to see if everything is working as expected after trying the suggested solution. If there’s anything else we can assist with, please don’t hesitate to ask.

If the issue is resolved, we’d appreciate it if you could mark the helpful reply as Accepted Solution — it helps others who might face a similar issue.

Warm regards,

Prasanna Kumar

[brockry1]

Nothing in this chat worked.  I figured it out on my own. 

[v-pgoloju]

Hi @brockry1,

Just following up to see if the solution provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.

If the response addressed your query, kindly mark it as Accepted Solution and click Yes if you found it helpful — this will benefit others in the community as well.

Best regards,

Prasanna Kumar

[Anonymous]

Hi @brockry1 ,

Did the reply offered help you solve the problem, if it helps, you can consider to accept it as a solution so that more user can refer to, or if you have other problems, you can offer some information so that can provide more suggestion for you.

Best regards,

Lucy Chen

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[brockry1]

What I'm trying to figure out is if you look at the last two screen shots I want to know which clinics are not carrying "483866" but are carrying the SKU's listed below it.  Like I said I ran a conditional column to say all the SKUs under 483866 is equal to Alt SKU "483866".  

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

[brockry1]

Vet Sell TO	486866	483817	483826	483828	484008	Total Alt SKU
Clinic 1		1		1		1
Clinic 2			1	1	1	3
Clinic 3		1	1	1		3
Clinic 4	1	1		1		2
Clinic 5	1		1			1

So basically I'm trying to figure out how to incorporate into the DAX formulas above with the time pramaters in them or a new DAX measure that will show me how many clinics that are not selling the main SKU in this case "483866" but are selling the Alt SKU which is a distinct count.  So in the example above it would be 3 clinics. 

[lbendlin]

first step is to bring your data into a usable format by unpivoting.

Then you can create a measure to compute the value based on the selected SKU

[brockry1]

I tried your formula and didn't exactly get back what I was expecting.  Below is a screen shot of more sample data if that helps. 

I already had it formatted in an unpivoted format I just couldn't figure out how to share my pbix file. 

[lbendlin]

final request: Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[brockry1]

I was trying to provide a larger sample size but could not figure out how to link an actual report vs just the sample table.  So I will try and provide a larger sample table.  Just for reference on the alternative skus that associate with the blockbuster skus. So for example in my power query I created a conditional column that groups the blockbuster sku with the alternative.  Example ( Blockbuster 483866 = Blockbuster SKU and Alt SKU 483866 is = 483817, 483826, 483826, 483828, 484008, 484022) and so on.  Expressed in earlier post end game is to see which clinics are not carrying the blockbuster sku but is carrying the alt sku.  

Sell-To	Item Code	Alt SKU	Level II Gross Sales  Value
Vet-xxxxxxx-xxx	427677	Blockbuster SKU	38.18
Vet-xxxxxxx-xxx	499618	Alt 499689	93.14
Vet-xxxxxxx-xxx	47071	Blockbuster SKU	200.26
Vet-xxxxxxx-xxx	483828	Alt 483866	48.25
Vet-xxxxxxx-xxx	44655	Blockbuster SKU	124.56
Vet-xxxxxxx-xxx	426025	Blockbuster SKU	38.18
Vet-xxxxxxx-xxx	426066	Blockbuster SKU	28.13
Vet-xxxxxxx-xxx	484366	Alt 470777	3.45
Vet-xxxxxxx-xxx	60434	Alt 44294	102.36
Vet-xxxxxxx-xxx	427625	Alt 427677	46.34
Vet-xxxxxxx-xxx	499618	Alt 499689	29.58
Vet-xxxxxxx-xxx	483866	Blockbuster SKU	67.29

[lbendlin]

I cannot assist you if you are unable to provide meaningful sample data. I hope someone else can help you further.