Help with getting attached rate and visualize them for each category?

[Anonymous]

I need a lot of help. I am trying to get the attached rate for main categories and tying visualize what they have attached on the main category. please see below, and data is by product level.  I will try my best to explain this and hopefully, this will make sense. 

Please see below picture

On Invoice level, each invoice has Accessory, Main category only or many category with an accessory or more attached. 

For sample data please click here

I want to know followings,

• How many are only Accessory 
• How many are only main category invoices 
• How many are attached when they by main category product   

Any idea of how to do this will really great. 

I am looking something like below or any other good way to show this.

Thank you so much for your time and help

[v-shex-msft]

Hi @Anonymous,
I have some thoughts, but I’m a bit puzzled regarding your three enquiries you've raised.

>>How many are only Accessory?

Measure 2 =
IF (
    SELECTEDVALUE ( Sales[Level4] )
        IN { "computer", "ipad", "iphone", "Pre-Owned CPU's and IOS" },
    BLANK (),
    CALCULATE (
        DISTINCTCOUNT ( Sales[InvoiceNumber] ),
        FILTER ( Sales, Sales[Level3] = "3. Accessories" )
    )
)

>>How many are only main category invoices?

Measure 3 =
CALCULATE (
    DISTINCTCOUNT ( Sales[InvoiceNumber] ),
    FILTER (
        Sales,
        Sales[Level3] = "Hero Products (iCU's)"
            && Sales[Level4] IN { "Pre-Owned CPU's and iOS", "Computer", "ipad", "iphone" }
    )
)

>>How many are attached when they by main category product?
I’m not quite sure what sort of effect you wanted. Please provide a more detailed description.

Best Regards,
Jack Chen

[Anonymous]

@v-shex-msft ,

Thank you so much for your help. There is a small issue. I a looking at this in the invoice level. For instance, when somebody buys a product from the main category (computer, iPad, iPhone, Pre-Owned CPU's and IOS)  sales agents should try to sell some accessory too. We call that attaching. If they did, we should see it on the invoice under the same invoice number.   What I am trying to do is try to get attached rate or get the invoices that attached accessory when the buy the main product, and only main product invoices. 

hopefully, this will make sense. Again, thank you so much for your time and help.