Need help on custom report

[Anonymous]

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	Cust Name	Product	Jan-21	Feb-21	Mar-21	Apr-21	May-21	Jun-21	Jul-21
North	101862	SHRIKRISHNA SALES	ABC 1	134	0	171	164	0	194	0
North	101862	SHRIKRISHNA SALES	ABC 3	342	0	160	112	0	180	0
North	101867	MATHURADAS JAMUNADAS & CO.	ABC 2	125	0	0	133	0	144	0
North	101867	MATHURADAS JAMUNADAS & CO.	ABC 1	231	0	0	890	0	55	0
North	102251	RAMDHAN AGARWAL	ABC 3	114	0	0	182	126	106	0
North	102251	RAMDHAN AGARWAL	ABC 2	342	0	160	112	0	180	0
East	102375	KAMAL AGENCIES	ABC 2	0	164	0	0	134	134	134
East	102375	KAMAL AGENCIES	ABC 1	114	151	0	182	126	106	0
East	102590	JITENDRAKUMAR BADAMILAL JAIN	ABC 2	0	133	0	125	125	0	0
East	102590	JITENDRAKUMAR BADAMILAL JAIN	ABC 3	114	151	0	182	126	106	0
East	102722	KABRA TRADING CO.	ABC 1	0	182	0	0	0	114	114
		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	3	2	9
		East	2	0	0	0	2

Thanks.

@VahidDM @Tanushree_Kapse @amitchandak 

[Icey]

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]

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]

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]

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

[Anonymous]

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]

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]

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

[Icey]

Hi @Anonymous ,

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

[Anonymous]

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]

@Anonymous 

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