Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
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.
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.
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.
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
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.
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 @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
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
How should the calculation work for "2 time purchase" , " 3 time purchase" and ">3 time purchase"?
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
User | Count |
---|---|
50 | |
23 | |
18 | |
18 | |
14 |
User | Count |
---|---|
91 | |
85 | |
46 | |
28 | |
21 |