Basket Analysis

[OpenMike13]

Hello i have a table that has a membership rate purchased by a contact and has a monthly date if the contact has a membership for the countdate.

ContactID	Countdate	Ratename
1	1/15/2022	Ind. Membership
1	2/15/2022	Ind Membership
1	3/15/2022	Deluxe Membership
2	1/15/2022	Ind. Membership
2	2/15/2022	Ind. Membership
2	3/15/2022	Gold Membership

question would there be a way to find the count/percentages of the combinations? For example the output would look like the table below

	Deluxe Membership	Gold Membership
Ind. Membership	50%	50%

[v-yanjiang-msft]

Hi @OpenMike13 ,

In my understanding, you want to get the ratio of the IDs owned by each membership on the last date to the total IDs. Is this your expected result:

Here's my solution, create a measure:

Count =
VAR _Num =
    COUNTROWS (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Countdate]
                = MAXX (
                    FILTER ( ALL ( 'Table' ), 'Table'[ContactID] = EARLIER ( 'Table'[ContactID] ) ),
                    'Table'[Countdate]
                )
                && 'Table'[Ratename] = MAX ( 'Table'[Ratename] )
        )
    )
VAR _TotalNum =
    CALCULATE ( DISTINCTCOUNT ( 'Table'[ContactID] ), ALL ( 'Table' ) )
RETURN
    DIVIDE ( _Num, _TotalNum )

Put the Ratename and the measure in a matrix, get the result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TheoC · Accepted solution]

Hi @OpenMike13 

Is "Ind. Membership" a type of membership as it is based in the Ratename column?

I've put together a PBIX with a few mods that align with the table in your post but just unsure if it's what you were wanting.

Let us know if it's not! 🙂

Theo

[OpenMike13]

Thanks for the quick response.

Ind. Membership is a type of membership but its a basic membership the gold and deluxe are upgraded memberships. I see you added a column to parse out the deluxe and gold. Would this be the route to go to find what tier they upgraded to from the original 'Ind. Membership'?

[TheoC]

Hi @OpenMike13 

To be honest, I only did that because that is what I saw in your requirement to get the output you needed. However, it would very much achieve exactly what you've raised as well (i.e. upgraded memberships).

Hope that helps 🙂

Theo

[OpenMike13]

What would be the best way to do that? 

[TheoC]

Hi @OpenMike13, as I've done for you is completely fine.  It provides you with the ability to use the original column for memberships but also the updated.  It's already been done in PowerQuery so there's no code needed in Power BIs front end.