The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I am trying to create groupings for a waterfall chart based off rankings to make my visual much easier to interpret. What I currently have is below:
I am attempting to make groups for this chart based off a ranking measure that I created for the Service Lines in terms of their Total $:
I attempted to create an IF statement where, if the Service Line rank was 1-4 it would show up as its own Service Line, and if its' rank was 5+ they would all be grouped into one category called "Other", but I could not get it to work. Is there a way to do this?
1st step is to create a disconnected table that will have your ranges and values you want to show ( I called it Groupings):
Then this is the measure to bring in the value of Label from the table above.
Banding = CALCULATE( VALUES( Groupings[Label] ) , FILTER ( ALL (Groupings), [Rank] >= Groupings[Start] && [Rank] < Groupings[End] ) )
[Rank] above references the measure you are using to get the actual rankings
And the final table:
I think this is what I'm looking for other than the fact that the 1-5 will all each have different specific service line names and not just "Service Line". Is that possible?
HI, @Anonymous
There is a same case i have solved for you refer to:
https://community.powerbi.com/t5/Desktop/Top-N-and-Others-monthly/m-p/490708#M228672
Hope it could help you.
Best Regards,
Lin
The easy way would be just extend the grouping table:
then use this measure:
Banding = CALCULATE( VALUES( Groupings[Label] ) , FILTER ( ALL (Groupings), [Rank] > Groupings[Start] && [Rank] <= Groupings[End] ) )
@Anonymous Sorry, I wasn't clear enough. I meant that I am trying to have this measure dynamic, so I would be able to use it for different clients where the 1-5 service line name could change. I am trying to have those be a measure to where it would equal their service line name and not hard code it into a column. Does that make sense?
hi, @Anonymous
Have you tried the solution what I've offered you, it should meet your requirement.
Best Regards,
Lin
@v-lili6-msft It is close to what I am looking for, however my biggest hurdle is the dynamic categories on the x-axis that your solution for the other problem doesn't really address
@v-lili6-msft this is my vision for what the chart would look like as a finished product (I manually created it without a measure just as an example)
The measure I am looking for would be able to change which categories show up on the x-axis depending on the different rankings for different clients.
hi, @Anonymous
Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?
Best Regards,
Lin
hi, @Anonymous
This is dynamic by the RANKX measure
You could use ALLSELECTED instead of ALL in the rankx measure
For example:
Ctry Rank = RANKX(ALL('Country with others'[Country Name]), 'Dim - Country'[Total])
Ctry Rank = RANKX(ALLSELECTED('Country with others'[Country Name]), 'Dim - Country'[Total])
If not your case, please share the sample data and with the expected output in different scenarios.
Best Regards,
Lin
It does, but those dynamic values have to come from somewhere. Do you have a table with them or something similiar. There has to something that relates a specific client to the row you want to bring in.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
141 | |
109 | |
108 | |
76 | |
63 |
User | Count |
---|---|
273 | |
129 | |
123 | |
101 | |
91 |