Forum Discussion

anandav's avatar
anandav
Icon for Skilled Sharer rankSkilled Sharer
5 years ago
Solved

How to Summarise VAR table - customer purchase analysis

Hi All,

I have a standard star schema of Sales, Products and Customers

 

I wanted to count how many customers have bought products ONLY in ONE category for the given period.

e.g. C1 and C2

 

I have a measure

1. Summarize sales by Customer and Category ID

VAR T1 = 
ADDCOLUMNS(
            SUMMARIZE(
                FILTER(ALL(Sales), Sales[SaleDate] >= DATE(2021, 01,01) )
                ,Customers[Cust ID]
                ,Products[Category ID]
            )
            ,"CatCount" , 1
        )

 

Now I wanted another VAR table T2 that will add CatCount by Customer 

 

My thinking is by filtering T2 where CategoryID = "PC1"  && CatCount = 1 should give me customers who have ONLY bought in Product Category PC1

 

Any suggestion how I can do this please?

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi anandav 

    You can try the measure as  below.

    Measure:

     

    _Category Only Customers =
VAR _T =
    FILTER (
        ADDCOLUMNS (
            Sales,
            "COUNT_",
                CALCULATE (
                    DISTINCTCOUNT ( Products[Product Category] ),
                    FILTER ( ALL ( Sales ), Sales[Customer] = EARLIER ( Sales[Customer] ) )
                )
        ),
        [COUNT_] = 1
            && [SaleDate] >= DATE ( 2021, 01, 01 )
    )
VAR _T2 =
    SUMMARIZE ( _T, [Customer] )
RETURN
    COUNTX ( _T2, [Customer] )

     

    Result is as below.

     

     

    Best Regards,

    Rico Zhou

     

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

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    anandav , Try a measure like

     

    countx(filter(
    SUMMARIZE(
    FILTER(ALL(Sales), Sales[SaleDate] >= DATE(2021, 01,01) )
    ,Customers[Cust ID]
    , "_Count", distinctCOUNT(Products[Category ID])
    )
    ,[_Count] =1
    ), [Cust ID])

    • anandav's avatar
      anandav
      Icon for Skilled Sharer rankSkilled Sharer
      5 years ago

      amitchandak ,

      Thank you for the reply.

      Using the above DAX I am getting the same value for all Product Categories.

      The PBI file is here.

       

       

      What I need is the Categories where customers have ONLY purchased in that category.

       

      Expected results based on attached PBI file is:

       

      Hope I have explained the requirement clearly.

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi anandav 

        You can try the measure as  below.

        Measure:

         

        _Category Only Customers =
VAR _T =
    FILTER (
        ADDCOLUMNS (
            Sales,
            "COUNT_",
                CALCULATE (
                    DISTINCTCOUNT ( Products[Product Category] ),
                    FILTER ( ALL ( Sales ), Sales[Customer] = EARLIER ( Sales[Customer] ) )
                )
        ),
        [COUNT_] = 1
            && [SaleDate] >= DATE ( 2021, 01, 01 )
    )
VAR _T2 =
    SUMMARIZE ( _T, [Customer] )
RETURN
    COUNTX ( _T2, [Customer] )

         

        Result is as below.

         

         

        Best Regards,

        Rico Zhou

         

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