Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 🤔
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.
@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.
Thank you already for all the help and I hope I wrote sothat you can understand my two main problems.
Have a great day!
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.
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 !!
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |