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      
ZoneCustCust NameProduct Jan-21Feb-21Mar-21Apr-21May-21Jun-21Jul-21
North101862SHRIKRISHNA SALESABC 1134017116401940
North101862SHRIKRISHNA SALESABC 3342016011201800
North101867MATHURADAS JAMUNADAS & CO.ABC 21250013301440
North101867MATHURADAS JAMUNADAS & CO.ABC 1231008900550
North102251RAMDHAN AGARWALABC 3114001821261060
North102251RAMDHAN AGARWALABC 2342016011201800
East102375KAMAL AGENCIESABC 2016400134134134
East102375KAMAL AGENCIESABC 111415101821261060
East102590JITENDRAKUMAR BADAMILAL JAINABC 20133012512500
East102590JITENDRAKUMAR BADAMILAL JAINABC 311415101821261060
East102722KABRA TRADING CO.ABC 10182000114114
           
  SummaryDist Count of Cust who purchased >0    
   purchase in launch monthFrom next month of launch month onwards to date    
  zone1 Time Purchase2 time purchase3 time purchase>3 time purchaseTotal   
  North31329   
  East20002   
           

 

Thanks.

VahidDM Tanushree_Kapse amitchandak 

DAX
DAX Commands and Tips

10 Replies

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    4 years ago

    Anonymous 

    How should the calculation work for "2 time purchase" , " 3 time purchase" and ">3 time purchase"?

  • 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 Icy , sorry for late reply.

      it is looking correct. going to implement this on my live data then let you know. thanks again for such a valuable reply.

      🙂

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