Clustering and Segmentation for subelements of Dataset within PowerBI

[Anonymous]

Hi together,

you already helped me once so I hope you might have a solution to my problem.

I already have a working formula that clusters my revenue and margin for the chart seen below sothat I can calculate that e.g. Customer 1 makes 190 revenue and Customer 2 125 revenue. I was also able to calculate an overal Margin for each customer (e.g. Customer 1 has 120/190 = 63%).

My Problem:

Now I want to have the margin specified to each department and later each year. Within my company I have such a dataset and already created several PBI Pages for each department. The Problem I have is that when I create a slicer for the margin it always applies to the overall margin of the customer across all department and across all years.

Is it possible to specify the formula for the profit margin in a way that it either only applies for the page filters (for example when I have a page for Department A it calculates the margin in % only for Department A)? Or is it possible within the dataset to create multiple calculations for Department A, Department B and Department C - if so: how do I have to specify my formula in DAX language?

I add the solution for the margin I currently have from another user (with no specification for the department here):

Margin total % = CALCULATE(DIVIDE(SUM('Table'[Margin]),SUM('Table'[Revenue])),ALLEXCEPT('Table','Table'[Customer ]))

cluster margin group = 
SWITCH(TRUE(),'Table'[Margin total %]>0.4,"Great",'Table'[Margin total %]>0.3&&'Table'[Margin total %]<=0.4,"Good",'Table'[Margin total %]<0.3,"Bad",BLANK())

How do I apply the formula only to a certain page filter. More concrete how can I easily create a A, B, C segmentation for Department A, Department B, Department C ? I also realized that I need this formula in order to classify the top customers for each year and not overall for multiple years as my dataset shows all revenues within one file (here a short table for clarification):

Customer	Revenue	Margin	Year	Month	Department
Customer 1	20	10	2017	...	Department A
Customer 2	50	15	2017	...	Department B
Customer 3	10	5	2017	...	Department A
Customer 1	80	50	2017	...	Department C
Customer 2	20	15	2018	...	Department A
Customer 3	15	10	2018	...	...
Customer 1	90	60	2019	...	...
Customer 2	55	20	2019	...	...
Customer 3	30	0	2019	...	...

So the goal is to afterwards apply the A, B, C segmentation for each Department and for each year. Do I need multiple calculated columns therefore?

I am sorry that I so consultation-intensive, but after this question I am really happy and satified

[Anonymous]

@Anonymous

Try edit the margin total% measure include the filters on Customer and Department using Earlier。

Margin total % = CALCULATE(DIVIDE(SUM('Table'[Margin]),SUM('Table'[Revenue])),filter(all(table),sumx(filter(table, earlier('Table'[Customer])='Table'[Customer]),1),filter(all(table),sumx(filter(table, earlier('Table'[Department])='Table'[Department]),1))

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.