Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
yaronklein
Helper II
Helper II

Bar chart challenge - set color for bars based on sum of the bars

Hi,
I have an interesting problem that I could not find an easy way to accomplish and I wonder if there is an easy (or a complex) way to acheive it.
Let's assume that I have the below simple bar chart.
I would like to color the bars that sums up to a value with one color while the rest will have another color.
For example, assuming that my target value is 350, I would like to color the first 2 bars in blue as they sum together to 340 and the rest will be in orange. If the target value will be 400, then I will color in blue the 3rd bar as well.
yaronklein_0-1651693361382.png


If you know of a way to achieve that it will be great.
Many thanks,
Yaron


 

 

1 ACCEPTED SOLUTION

@yaronklein  Thanks for the explanation.

 

Here is the solution. Hope this meets your requirement.

Running Total New = IF([Points Total] > 0,

CALCULATE (
SUM ( Issues[Points] ),
FILTER (
ALLSELECTED ( EpicMaster ),
ISONORAFTER ( EpicMaster[Rank], MAX ( EpicMaster[Rank] ), DESC )
)
),
BLANK())

 

Color Code = 
VAR rt = Issues[Running Total New]
VAR threshold = 100
Return
IF(rt <= threshold, "Blue", "Red")
ghoshabhijeet_0-1653321077826.pngghoshabhijeet_1-1653321092026.png

 

** If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution, so that it comes in top of the search and help others. Thank you !

Good Luck 👍

 

View solution in original post

27 REPLIES 27

Hi ghoshabhijeet,
Again, I apologize if it is not clear enough.
Everyhting is ok and the solution is almost there, except that the order of the epics in the chart graph needs to be done by the rank. Like in the below example that is taken from the uploaded file in message 22.

yaronklein_0-1653255556836.png

 


I found a mistake in the sorting of the graph and this is maybe what confused things up.
I fixed it and the above picture is what you get now.
I tried to use the same logic and functions that you used with the rank, but I couldn't figure out how to make it work.
So basically, assuming the rank is what drive the sort, if the limit is 100 - epic 3, 1 and 5 should be colored blue as they sum together 75 and the next one, epic 2 , has 60 points that will be bring the sum to 135 which is above 100.

Just to make sure that the bottom table doesn't confuse you, the columns 'Running Total New' and 'Color code' show the old values by the epic order and not what I need. 
If it will work correctly it will show:
Epic 3 | Total Points = 10 | Running Total Points - 10   | Color Code - blue
Epic 1 | Total Points = 50 | Running Total Points - 60   | Color Code - blue
Epic 5 | Total Points = 15 | Running Total Points - 75   | Color Code - blue
Epic 2 | Total Points = 60 | Running Total Points - 135 | Color Code - red
Epic 4 | Total Points = 5  | Running Total Points - 140  | Color Code - red
... the rest will be red.

I hope that this clears things up.

Thanks,
Yaron



@yaronklein  Thanks for the explanation.

 

Here is the solution. Hope this meets your requirement.

Running Total New = IF([Points Total] > 0,

CALCULATE (
SUM ( Issues[Points] ),
FILTER (
ALLSELECTED ( EpicMaster ),
ISONORAFTER ( EpicMaster[Rank], MAX ( EpicMaster[Rank] ), DESC )
)
),
BLANK())

 

Color Code = 
VAR rt = Issues[Running Total New]
VAR threshold = 100
Return
IF(rt <= threshold, "Blue", "Red")
ghoshabhijeet_0-1653321077826.pngghoshabhijeet_1-1653321092026.png

 

** If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution, so that it comes in top of the search and help others. Thank you !

Good Luck 👍

 

ghoshabhijeet,
That's it, you nailed it!!!!
Kudos to you.
I cannot thank you enough for your persistence to solve the problem.
Until next time 🙂
Yaron

Sure .. Welcome @yaronklein 😊👍

Hi ghoshabhijeet,
I am currently out of office and I will try it on Friday when i will be back or over the weekend.
Thank you!
Yaron

Will do.
I will simplify it and will remove data so i could share it and i will upload it later on today or tomorrow.
Thanks again!

ghoshabhijeet
Solution Supplier
Solution Supplier

@yaronklein  You can try this approach.

I have created a sample data table. Snapshot below:

 

ghoshabhijeet_0-1651697761646.png

Now, once you have your data table. You need to create a measure for running total.

Running Total = 

VAR catrank =

RANKX ( ALL ( 'DataTable'[Category] ), 'DataTable'[Total Val],, DESC, DENSE )

VAR RunningToT =

CALCULATE (

'DataTable'[Total Val],

FILTER (

ALL ( 'DataTable'[Category] ),

catrank >= RANKX ( ALL ( 'DataTable'[Category] ), 'DataTable'[Total Val],, DESC, DENSE )

)

)

RETURN
RunningToT

Create another measure for Color Code:

 

Color Code =
VAR runningtot = [Running Total]
VAR threshold = 25
RETURN
IF ( runningtot <= threshold, "Blue", "Red" )

 Once the measures are created. If you pull this measures in a table it will look like in the snapshot below:

ghoshabhijeet_2-1651698195349.png

Now, using the above measures create a Clustered Bar Chart and for the bar color use the measure created for Color Code for Conditional Formatting. The output will look like this:

ghoshabhijeet_3-1651698304632.png

** If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution, so that it comes in top of the search and help others. Thank you !

Good Luck 👍

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.