Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
michaelsh
Kudo Kingpin
Kudo Kingpin

Top product combinations sold - DAX measure

I need to create a Measure that shows which Two-Product Combinations are most common.

For example, here A,B is most common, because 3 customers have it.

Although D is the most common product with 4 customers, but I don't want to see it in my measure, because it is sold alone, not as a combination.

So I need my measure to return "A,B"

Thanks!

CustomerProduct
111A
111B
111C
222X
222Y
222Z
333D
444D
555D
666D
777A
777B
888A
888B
999X
999Y
2 ACCEPTED SOLUTIONS

Basket analysis is probably the best way (didn't confirm), but here is another approach.  Put this measure in a card to get "A,B" as the result.

 

Highest Combo =
VAR cj1 =
    DISTINCT ( SELECTCOLUMNS ( Products, "Product1", Products[Product] ) )
VAR cj2 =
    DISTINCT ( SELECTCOLUMNS ( Products, "Product2", Products[Product] ) )
VAR cj =
    FILTER ( CROSSJOIN ( cj1, cj2 ), [Product1] <> [Product2] )
VAR summary =
    ADDCOLUMNS (
        cj,
        "@total",
            VAR prod1 = [Product1]
            VAR prod2 = [Product2]
            RETURN
                COUNTROWS (
                    FILTER (
                        ALL ( Products[Customer] ),
                        AND (
                            NOT ( ISBLANK ( CALCULATE ( COUNTROWS ( Products ), Products[Product] = prod1 ) ) ),
                            NOT ( ISBLANK ( CALCULATE ( COUNTROWS ( Products ), Products[Product] = prod2 ) ) )
                        )
                    )
                )
    )
VAR productslist =
    CONCATENATEX (
        TOPN ( 1, summary, [@total], DESC, [Product1], ASC ),
        [Product1] & ", " & [Product2]
    )
RETURN
    productslist

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.  With some more effort, i may be able to get A&B but you may take it forward from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

6 REPLIES 6
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.  With some more effort, i may be able to get A&B but you may take it forward from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@amitchandak 

Yes, this is a Basket Analysis question.

I have researched all these links before I posted. All these topics are similar but they are not answering my case.

I am looking for a specific solution for my case, please.

Thank you

Basket analysis is probably the best way (didn't confirm), but here is another approach.  Put this measure in a card to get "A,B" as the result.

 

Highest Combo =
VAR cj1 =
    DISTINCT ( SELECTCOLUMNS ( Products, "Product1", Products[Product] ) )
VAR cj2 =
    DISTINCT ( SELECTCOLUMNS ( Products, "Product2", Products[Product] ) )
VAR cj =
    FILTER ( CROSSJOIN ( cj1, cj2 ), [Product1] <> [Product2] )
VAR summary =
    ADDCOLUMNS (
        cj,
        "@total",
            VAR prod1 = [Product1]
            VAR prod2 = [Product2]
            RETURN
                COUNTROWS (
                    FILTER (
                        ALL ( Products[Customer] ),
                        AND (
                            NOT ( ISBLANK ( CALCULATE ( COUNTROWS ( Products ), Products[Product] = prod1 ) ) ),
                            NOT ( ISBLANK ( CALCULATE ( COUNTROWS ( Products ), Products[Product] = prod2 ) ) )
                        )
                    )
                )
    )
VAR productslist =
    CONCATENATEX (
        TOPN ( 1, summary, [@total], DESC, [Product1], ASC ),
        [Product1] & ", " & [Product2]
    )
RETURN
    productslist

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Hi Pat, @mahoneypat 

Do you hve any tips I could do the same but with combination of 3 products? 

Best Regards,
Anita

Or how can I retrieve the table with eg. top 10 product combos within certain date frame selected on slicer? 
Is that possible? 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.