I’ve spent time trying to work this out but no success. Leaving aside the segmentation word the Insight I am looking for is essentially:
Each segment is made up of a number of customers that varies depending on certain business rules. The higher segments have fewer customers, but they spend more.
What I get each Q is the table that classifies a Customer ID as one of Plat, Gold, Silver etc and the Quarter that relates to. A customer can move across PGSBT, and that is the valuable piece of Insight - why did a customer move? how do you move them back up to remain valuable?
The CustomerSegmentbyQ table is the table that list, Segment, Quarter, Customer ID. Where a customer resides for each quarter.
This has a link to the InvoiceTable and I’ve tried to create this link by connecting the Quarter to all the dates, (QQ YYYY Link) to allow all the invoices for that period to be totalled. But when I try this I’m not getting any correct Sales Totals.
Segment Example SC1.pbix (1.5 MB)
So that makes me think there is something wrong with the data model so I’ll change it. I’ve added a Many:1 relationship from the CustomerSegmentbyQ table to the Cust Ref table. My thinking being that the CustSegbyQ table has a unique Cust ID for each Seg / Q combination, which should then allow a unique customer Id to be found in the reference table and then all those invoices totalled. But alas no.
Segment Example SC2.pbix (1.5 MB)
I have spun round various iterations and not got a result.
The core tables are as in these examples. One way round it is to make CustomerSegmentbyQ effectively a Fact Table. Populate it with Sales and all other totals outside of BI and just load it in. That will allow Visualisations to be created etc.
But I think there must be another way. One way I tried exploring, but couldn’t get the syntax correct is to create VAR which tried to pass the Customer ID List for each Q / Segment combination to a Calculation, which I think would work.
So just need help with:
Is it too complex for Power BI and I should do heavy processing outside
Is it something that is relatively straightforward just beyond my current skill set.
If so, is the answer in VAR and some virtual table magic I don’t yet understand