Dynamic segmentation with criteria depending on the customer group

[Ghuiles]

Hello

I have a Sales fact table, based on which i calculte the sum of sales, sum or cost, and then the margin in %.

The table is narrow, so all amounts are in only one column.

The classification of the customer is based on the Margin% (negative, low, high).

Now the difficulty i am having, is that i have different customer groups (not too many) for which the definition of what is low or high ist different.

Say for example:

	Min Cust Grp A	Min Cust Grp B	Max Cust Grp A	Max Cust Grp B
Profitable customers	3%	4%	9999%	9999%
Low margin customers	0%	0%	3%	4%
Negative margin customers	-9999%	-9999%	0%	0%

I understood that the method Dynamic Segmentation (https://www.daxpatterns.com/dynamic-segmentation/) could help me, but how can i make the threshold in the formula depending on the customer group?

I already spent some time on this, so any help would be apreciated.

Cheers. G.

[Mariusz]

Hi @Ghuiles 

You can build a segment table like below, and add a relationship with Cust Grp or Use TREATAS function.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Ghuiles]

Hi Mariusz!

Thanks @Mariusz  for your very fast reply!

But I am surprised. I thought that the segmentation table HAS TO be without relathionship to the fact table.

Or did i miss something?

In your proposal, the model with the relationships will then be like this:

Fact Table <-- DimCustomers Table <-- DimCustomersGrp Tabl <-- Sementation table.

Right?

Cheers. G.

[Mariusz · Accepted solution]

Hi @Ghuiles 

Yes the scenario in the article "Dynamic Segmentation // DAX patterns" does not include relationships as you calculating the values between Min and Max, however, you have introduced an extra concept of Customer Groups and you want the model to obey this filters so relationship or using DAX is necessary.

Hope this helps.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Ghuiles]

Many Thanks @Mariusz  😉

G.

[Ghuiles]

@Mariusz it seems to work 🙂 

So, many thanks to you 🙂

And on the top of that, I learned then quite something 🙂

I will have to continue my checks. I will let you know if something does not match.

Cheers. G.