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 ...
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. 

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. 

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

      Anonymous 

      Thanks a lot. That works.

      Really appreciate your help. 

       

      Ps. Sorryfor the late reply - off work due to some issues.