Forum Discussion
group customer by orders
Hi again Anonymous
I updated my PBIX with the same dataset you have used in your Tableau workbook.
My existing measure is actually sufficient to do what you want.
I created the same visual you had in Tableau:
- Created First Purchase Date calculated column in Orders and related this to a First Purchase Date table:
Date of First Purchase = CALCULATE ( MIN ( Orders[Order Date] ), ALLEXCEPT ( Orders, Orders[Customer ID] ) )Note that I hid this column in Report View - Created Monts Since First Purchase calculated columns:
Months Since First Purchase = VAR MonthIndex = DATEDIFF ( Orders[Date of First Purchase], Orders[Order Date], MONTH ) RETURN "M" & FORMAT ( MonthIndex, "00" ) - Applied a Page Level Filter, Segment Name = "1 to 5"
- Placed my earlier measure Number of Customers by Order Count Segment in a matrix visual, with some conditional formatting similar to your Tableau example.
You could also create measures that apply particular segment filters, such as
Number Customer Purchase 1 to 5 = CALCULATE ( [Number of Customers by Order Count Segment], Segment[Segment Name] = "1 to 5" )PBIX attached. Please post back if needed.
Regards,
Owen
Note that when including both Months Since First Purchase and First Purchase Month on the visual , all customers have five or fewer orders.
- Created First Purchase Date calculated column in Orders and related this to a First Purchase Date table:
Hi,
First, I set a measure called Order 1-5 by IF statement
Then, for each customers who has order 1-5, let count the number of customers.
That is my formlar, if you need workbook Tablaeau, I can send it to you!!
Thanks
- OwenAuger
Super User
Thanks for the explanation.
Yes, if you could provide a link to the Tableau workbook, that might make it easier to ensure we're producing the same thing in Power BI.
The existing measure I posted does count the number of customers within each segment, but does so within the current filter context. We might need to modify this measure to ignore certain filters when deciding which segment a customer is in.
I will take a closer look at what you've done in Tableau first.
Regards,
Owen
Dear Owen,
Thank you for your reply.
Here is the workbook in tableau and the result the I expect also.
Please have a look at that
Thank you and hope to hear from you soon.
- OwenAuger
Hi again Anonymous
I updated my PBIX with the same dataset you have used in your Tableau workbook.
My existing measure is actually sufficient to do what you want.
I created the same visual you had in Tableau:
- Created First Purchase Date calculated column in Orders and related this to a First Purchase Date table:
Date of First Purchase = CALCULATE ( MIN ( Orders[Order Date] ), ALLEXCEPT ( Orders, Orders[Customer ID] ) )Note that I hid this column in Report View - Created Monts Since First Purchase calculated columns:
Months Since First Purchase = VAR MonthIndex = DATEDIFF ( Orders[Date of First Purchase], Orders[Order Date], MONTH ) RETURN "M" & FORMAT ( MonthIndex, "00" ) - Applied a Page Level Filter, Segment Name = "1 to 5"
- Placed my earlier measure Number of Customers by Order Count Segment in a matrix visual, with some conditional formatting similar to your Tableau example.
You could also create measures that apply particular segment filters, such as
Number Customer Purchase 1 to 5 = CALCULATE ( [Number of Customers by Order Count Segment], Segment[Segment Name] = "1 to 5" )PBIX attached. Please post back if needed.
Regards,
Owen
Note that when including both Months Since First Purchase and First Purchase Month on the visual , all customers have five or fewer orders.
- Created First Purchase Date calculated column in Orders and related this to a First Purchase Date table: