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.     ContactID Countdate Ratename 1 1/15/20...
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

     

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.