Forum Discussion

OpenMike13's avatar
OpenMike13
Frequent Visitor
4 years ago
Solved

Basket Analysis

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.

 

 

ContactIDCountdateRatename
11/15/2022Ind. Membership
12/15/2022Ind Membership
13/15/2022Deluxe Membership
21/15/2022Ind. Membership
22/15/2022Ind. Membership
23/15/2022Gold 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 MembershipGold Membership
Ind. Membership50%50%
Need Help
  • TheoC's avatar
    TheoC
    4 years ago

    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

     

6 Replies

  • TheoC's avatar
    TheoC
    Community Champion
    4 years ago

    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's avatar
      OpenMike13
      Frequent Visitor
      4 years ago

      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's avatar
        TheoC
        Community Champion
        4 years ago

        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

         

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    4 years ago

    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.