Clustering within PowerBI with DAX - e.g. ABC revenue analysis & calculation overall Profit Margin

[Anonymous]

Hi together,

first off: I am quite new to the Forum, PowerBI and DAX , but I want to use it way more and now I am trying to do training on the job and self education. With the standard visuals and drillthroughs I am already able to get some good looking sales data, but now I am at a point where I really need your help by first clusting the values of my database and then evaluate them (I am german and thus my spelling is not that good sorry - the examples should make my point clearer). 

The problem I have is that I am not capable of applying one criteria "tell me if the total revenue of a customer is bigger than 2,000$" on multiple lines and not on one line only. (I attached a picutre which shows the problem more clearly I hope).

Within Excel I would have the solution and work with a pivot table which shows the revenue and margin of a the customers in total and then evaluate if the customer has a total revenue greater than X, Y, Z. Same with the Overall Margin of the customer and not the Margin of each single line (e.g. each product).

The attached picture shows what I want to achieve in PowerBI. I have 2 criteria "Total Revenue - ABC Matrix" and "Profitability Margin (total)". Then I have the simple Dataset and last I have a Pivot which helps me in Excel. When I then have the selection criteria A, B, C and Margin in BI I can dive deeper with drillthrough to each product - at least this is the plan 😉 .

The reason I want to dive into PowerBI with that is that I already have multiple around 300k lines in each 90 MB Excel files which more and more explodes and PowerBI allows me (and my company) to work way better with these datasets.

Hope my spelling and overall question is not too confusing. Please help me 🤔

[v-kelly-msft]

Hi @Anonymous ，

You need 3 measures as below:

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

Cluster revenue = 
var a =CALCULATE(SUM('Table'[Revenue]),ALLEXCEPT('Table','Table'[Customer ]))
Return
SWITCH(TRUE(),a>200,"A",a>100&&a<=200,"B",a<30,"C",BLANK())

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())

Finally you will see:

For the related .pbix file,pls click here.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

@v-kelly-msft Wow! Thank you very much for the fast and good reply 🙂

You really pushed my PowerBI Application forward 🤗

Unfortunately I still have a few issues where you might could help me.

1. First of is that I tried to generate a "Slicer" and "visual Filter" for two of the measures you / we created.
   I already searched this forum but did not really come close. I found this: https://community.powerbi.com/t5/Desktop/Cannot-use-measure-in-slicer/m-p/168236#M73486 but I was not able to apply it to the dataset even though I tried for a long time 😞 Could you help with the calculation sothat I get a visual clickable filter for "Cluster Revenue" sothat one can press "A", "B" or "C". Then I also want to create a slicer for the "Margin total %" sothat one can slide and lets say show the customers with margin total % between 20% and 35%.
2. Is there a way I can update / specify the formula of "cluster revenue" sothat it applies to the page filters in PowerBI?
   For clarification: My dataset has several customers which make revenue in multiple departments. Now I created a "Page" for department A with the page filter "department A" sothat all revenues and other values only show the ones for department A. When I now apply the cluster revenue formula it automatically clusters the revenue of the whole customer without looking only on department A.
   Question: Is it possible to implement that either I add a line to the cluster calculation with specifications for "Department A" or maybe that the calculation looks up itself which filters are applied to the page? Both solutions would be very helpful and then I am 100% satified 🙂

Thank you already for all the help and I hope I wrote sothat you can understand my two main problems.

Have a great day! 

[v-kelly-msft]

Hi @Anonymous ，

If you wanna slice for "Cluster Revenue" by "A,B,C",you'd better change the relative measure into a calculated column,using below dax expression:

Column _Cluster revenue = 
var a =CALCULATE(SUM('Table'[Revenue]),ALLEXCEPT('Table','Table'[Customer ]))
Return
SWITCH(TRUE(),a>200,"A",a>100&&a<=200,"B",a<30,"C",BLANK())

And in the sample data,no line meets the requirement with C, so if you want a slicer to contain all the categories "A,B,C",you'd better create a dim slicer table:

Then create a measure as below:

Measure = IF(SELECTEDVALUE('slicer table'[cluster revenue]) in FILTERS('Table'[Column _Cluster revenue])&&SELECTEDVALUE('slicer table'[cluster revenue])<>BLANK(),1,IF(SELECTEDVALUE('slicer table'[cluster revenue])=BLANK(),1,BLANK()))

In the filter ,choose measure =1,Then you can easily slicer the table by revenue.

As for Margin total%,it is a same issue,just modify the measure to a calculated column,see below:

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

But if you wanna slice between 20%-35%,as the range is out of the range of the result in Margin total ,so you'd better use the same way to create a dim slicer table containing the ranging you need.

Finally you will see:

I have modified the pbix file per your request,pls click here.

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

Hi @v-kelly-msft ,

thanks again for the fast and great help! It took some time but I understood both steps and they were very helpful! 🙂 we are getting very close to what I need. Thank you so much!!

You fully answered my first question, but could you also help me with the second one?

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	Year	Month	Department
Customer 1	20	2017	...	Department A
Customer 2	50	2017	...	Department B
Customer 3	10	2017	...	Department A
Customer 1	80	2018	...	Department C
Customer 2	20	2018	...	Department A
Customer 3	15	2018	...	...
Customer 1	90	2019	...	...
Customer 2	55	2019	...	...
Customer 3	30	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 🙂

Thank you so much already @v-kelly-msft !! 

[Anonymous]

Hi @v-kelly-msft , 

is it possible to answer my last question? I am still stuck at the point where I want to apply the filter within only a single PBI page. Or in other words that I can tell the filter that he only should cluster e.g. revenue within one department.

I tried ti set up a table which you can see above which shows the current case.

Is it possible for you to help me for the last time?

This would be great!

Wish you all the best Florian

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Perhaps something like this? https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-ABC-Classification/m-p/479146#M180