Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Need help on custom report

Hi, Expert,   Need one help regarding this report. I am doing this in excel but not able to make in Power BI. Need one help please           launch month             Zone Cust ...
DAX
DAX Commands and Tips
Icey's avatar
Icey
Icon for Community Support rankCommunity Support
4 years ago

Hi Anonymous ,

 

To my understand, the expected result is like below. If there is any misunderstanding, please let me know.

Summary Dist Count of Cust who purchased >0        
  purchase in launch month From next month of launch month onwards to date      
zone 1 Time Purchase 2 time purchase 3 time purchase >3 time purchase Total
North 3 1 1 1 6
East 2 0 1 2 5

 

I UnPivot your data and do some transformations. Please check the attached .pbix file.

Count = 
SWITCH (
    MAX ( Times[Time] ),
    "1",
        CALCULATE (
            DISTINCTCOUNT ( 'Table'[Cust] ),
            FILTER (
                ALLEXCEPT ( 'Table', 'Table'[Zone] ),
                'Table'[Sales] > 0
                    && 'Table'[launch month]
            )
        ),
    ">3",
        VAR t =
            FILTER (
                SUMMARIZE (
                    'Table',
                    'Table'[Zone],
                    'Table'[Cust],
                    "MonthCount_",
                        CALCULATE (
                            DISTINCTCOUNT ( 'Table'[Month] ),
                            FILTER (
                                ALLEXCEPT ( 'Table', 'Table'[Zone], 'Table'[Cust] ),
                                'Table'[Sales] > 0
                                    && NOT ( 'Table'[launch month] )
                            )
                        )
                ),
                [MonthCount_] > 3
            )
        RETURN
            COUNTAX ( t, [Cust] ),
    VAR t =
        FILTER (
            SUMMARIZE (
                'Table',
                'Table'[Zone],
                'Table'[Cust],
                "MonthCount_",
                    CALCULATE (
                        DISTINCTCOUNT ( 'Table'[Month] ),
                        FILTER (
                            ALLEXCEPT ( 'Table', 'Table'[Zone], 'Table'[Cust] ),
                            'Table'[Sales] > 0
                                && NOT ( 'Table'[launch month] )
                        )
                    )
            ),
            [MonthCount_] = VALUE ( MAX ( Times[Time] ) )
        )
    RETURN
        COUNTAX ( t, [Cust] )
)

Count 2 = 
SUMX ( VALUES ( Times[Time Purchase] ), [Count] ) + 0

 

 

Best Regards,

Icey

 

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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Thanks its looks correct as have done basis on this help.

 

But having one point more, trying to find retetion customers count. for example

there are 100 unique cust in jan21 but only purchased 50 in feb hence retetioned customer count is 50 

and in march60 unique customer purchased hence retetioned customer are 60 .

below is the summary.

I have colored the row in raw data and in summary for clarifications.

 

 

thanks.

cy_icy Fowmy Tanushree_Kapse VahidDM amitchandak 

  • Icey's avatar
    Icey
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous ,

     

    Try to create a measure like so:

    Retetioned - Unique Customers count =
VAR Customers_launchmonth =
    CALCULATETABLE (
        DISTINCT ( 'Table'[Cust] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Zone] ),
            'Table'[launch month]
                && 'Table'[Sales] > 0
        )
    )
VAR Customers_othermonth =
    CALCULATETABLE (
        DISTINCT ( 'Table'[Cust] ),
        NOT ( 'Table'[launch month] ),
        'Table'[Sales] > 0
    )
RETURN
    COUNTROWS ( INTERSECT ( Customers_launchmonth, Customers_othermonth ) ) + 0

     

     

    Best Regards,

    Icey

     

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks Icey,

      in above logic you have checked in launch month as well .

      I have to count those customers which sales>0 nd atleast purchase 1 time in any previous month.

      I am trying to do basis on above logic but not getting correct numbers.

       

      can you please check where I am doing any mistake. Thanks

      Retention Customers =
      VAR t =
      FILTER (
      SUMMARIZE (
      Repeat_Analysis_Table,
      Repeat_Analysis_Table[Dist+finalvittrakcode],
      Repeat_Analysis_Table[Final Channel],
      "MonthCount_",
      CALCULATE (
      DISTINCTCOUNT ( Repeat_Analysis_Table[MOC_Name_Auto_New]),
      FILTER (
      ALLEXCEPT ( Repeat_Analysis_Table,
      Repeat_Analysis_Table[Dist+finalvittrakcode],
      Repeat_Analysis_Table[Final Channel] ),
      Repeat_Analysis_Table[Repeat Flag] > 0
       
      )
      )
      ),
      [MonthCount_] > 1
      )
      RETURN
      COUNTAX ( t, Repeat_Analysis_Table[Dist+finalvittrakcode] )
      • Icey's avatar
        Icey
        Icon for Community Support rankCommunity Support
        4 years ago

        Hi Anonymous ,

         

        Try this:

        Retention Customers =
VAR CurrentMonth_ =
    MAX ( 'Table'[Month - Copy] )
VAR Customers_previousmonths =
    CALCULATETABLE (
        DISTINCT ( 'Table'[Cust] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Zone] ),
            'Table'[Month - Copy] < CurrentMonth_
                && 'Table'[Sales] > 0
        )
    )
VAR Customers_currentmonth =
    CALCULATETABLE ( DISTINCT ( 'Table'[Cust] ), 'Table'[Sales] > 0 )
RETURN
    COUNTROWS ( INTERSECT ( Customers_currentmonth, Customers_previousmonths ) ) + 0

         

        Best Regards,

        Icey

         

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi, Thanks for such great help.

      but when I am apply this logic on my current data , not getting actual result.

      as I have to distinct count of those customers who have purchased min two times till that month including that month.

      for example 1 customer purchase in jan21 and again purchase in feb21 then if we count of purchase till feb then it is 2 times then we will count in this retention logic.

      seeking help on such logic in DAX .

      Please help me 

      Vahid-DM amitchandak Tanushree_Kapse Icey PowerBI .

      Thanks in advance