Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
If you know of a way to achieve that it will be great.
Many thanks,
Yaron
Solved! Go to 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")
** 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 👍 |
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.
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")
** 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
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!
@yaronklein You can try this approach.
I have created a sample data table. Snapshot below:
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:
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:
** 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 👍 |
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |