cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
shankarshiva70
Helper III
Helper III

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 

10 REPLIES 10
Icey
Community Support
Community Support

Hi @shankarshiva70 ,

 

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

 

purchase.PNG

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.

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.

shankarshiva70_1-1635250992585.png

 

 

thanks.

@cy_icy @Fowmy @Tanushree_Kapse @VahidDM @amitchandak 

Hi @shankarshiva70 ,

 

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

Icey_0-1635400908843.png

 

 

Best Regards,

Icey

 

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

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

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] )

Hi @shankarshiva70 ,

 

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

Icey_0-1635484117646.png

 

Best Regards,

Icey

 

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

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

Hi @shankarshiva70 ,

 

I think my measure above is on the same calculation logic as what you mentioned.

 


 

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

 


Where is the problem? In what situations are the returned results incorrect?

 

 

Best Regards,

Icey

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.

🙂

Fowmy
Super User
Super User

@shankarshiva70 

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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors