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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
JoshP11
Helper II
Helper II

Advice on best visual to use for target value of 0

Hello,

 

I'm looking for some advice on what visual would be best to use for what I need to display.

 

I have a value that can either be a - or + number ( lets say -15 or 15). 

 

The closer to 0, which is the target value, the better.

 

Can anyone recommend a visual or free download visual that could display something that makes this clear? I've already put in some conditional formatting for a card visual, which is fine but surely there must be some better way of showing this.

 

Thanks!

1 ACCEPTED SOLUTION

Hi @JoshP11 

I apologize, but I find it very difficult to understand what the formula returns when there is no database.

Therefore, unfortunately, without access to the file, I can only provide general guidelines:

  • The Y-axis value, in the case of a race chart, is meaningless because it's only used to position the line somewhere within the chart space.
  • Everything about the four points is completely dynamic, representing four different measures:
    • A measure for the minimum point
    • A measure for the maximum point
    • A measure for the target value
    • And a measure for the actual performance

So, anything that works for the card will work here as well. Starting with the X-axis, which consists of a table that ranges from the minimum to the maximum, these can be measures, and everything related to the design of the points. The logic I outlined in the attached file should be able to apply to any measure you create.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

View solution in original post

12 REPLIES 12
Ritaf1983
Super User
Super User

Hi @JoshP11 
What is the purpose of the graph? Is it just to show a single measure at the level of one parameter against a target? Or is it meant to compare multiple categories, such as temperatures by country?

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

Hi @Ritaf1983 it's used to show which way a value swings more towards.

 

There is a calculation working behind it, but essentially if its a + number, then the right side is 'stronger', if it's a - number, then the left side is 'stronger'. The goal is for right and left to be as equal as possible.

 

I understand it's a unique scenario but was hoping there could be something!

Hi @JoshP11 

I have to admit that I still don’t fully understand the goal, but I'll give it a try—who knows, it might just work!

If the goal is to show where the current value falls between the minimum and maximum, perhaps you could use a horizontal "race chart"-style visualization and use the color of the dot to indicate whether it’s within an acceptable range.

Ritaf1983_0-1728649927406.png

Of course, you could make the chart cleaner—perhaps just use an icon or label for the target and the current value, and highlight only the current value in a different color. This is just an example of the style.

Alternatively, you could use something similar to a boxplot.

Ritaf1983_1-1728650670182.png
If you're simply comparing numbers between positive and negative, you could use a basic bar chart with their absolute values, as there’s nothing easier to compare than two vertically stacked rectangles!

I'm attaching the PBIX file with examples. Note, in the desktop version, the race chart doesn't look great, but it aligns properly after publishing.

Ritaf1983_2-1728650841196.png

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

 

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

Hi @Ritaf1983  - thank you so much for your help, the race chart visual is exactly what I had in mind in terms of how to visualise the data, however I'm struggling to replicate it exactly as you have as the way I get my value is a little complicated.

 

I'm currenly using this measure to check what test has been selected from a slicer, check the date falls between the past 21 days, then it does my calculation to return the value I need, and if the test type for either L or R is selected in the drop down, return the value.

 

< 21Days =
VAR SelectedTestTypes = CONCATENATEX(VALUES(ParameterTable[Test Types]), ParameterTable[Test Types], ",")
VAR DateFilteredTable =
FILTER(
'DATA',
'DATA'[Date] >= TODAY() - 21 && 'DATA'[Date] <= TODAY()
)
VAR ABDL = MAXX(DateFilteredTable, 'DATA'[TEST1 L])
VAR ABDR = MAXX(DateFilteredTable, 'DATA'[TEST1 R])
VAR ADDL = MAXX(DateFilteredTable, 'DATA'[TEST2 L])
VAR ADDR = MAXX(DateFilteredTable, 'DATA'[TEST2 R])

VAR TEST1Difference = TEST1L - TEST1R
VAR TEST1MaxValue = MAX(TEST1L, TEST1R)
VAR TEST1Result = IF(NOT ISBLANK(TEST1L) && NOT ISBLANK(TEST1R), DIVIDE(TEST1Difference, TEST1MaxValue, 0), BLANK())

VAR TEST2Difference = TEST2L - TEST2R
VAR TEST2MaxValue = MAX(TEST2L, TEST2R)
VAR TEST2Result = IF(NOT ISBLANK(TEST2L) && NOT ISBLANK(TEST2R), DIVIDE(TEST2Difference, TEST2MaxValue, 0), BLANK())
RETURN
IF(
CONTAINSSTRING(SelectedTestTypes, "TEST1 L") && CONTAINSSTRING(SelectedTestTypes, "TEST1 R"),
TEST1Result,
IF(
CONTAINSSTRING(SelectedTestTypes, "TEST2 L") && CONTAINSSTRING(SelectedTestTypes, "TEST2 R"),
TEST2Result,
BLANK()
)
)

 

Basically I need the return value to be dynamic and show based on what is selected on the drop down. As i mentioned before, I can get it working where the number returns in a data card but I guess it's now figuring out how I get this into what you suggested.

 

Appreciate the above complicates things but if you have any suggestions that would be great.

 

Thanks again

 

 

 

 

 

 

Hi @JoshP11 

I apologize, but I find it very difficult to understand what the formula returns when there is no database.

Therefore, unfortunately, without access to the file, I can only provide general guidelines:

  • The Y-axis value, in the case of a race chart, is meaningless because it's only used to position the line somewhere within the chart space.
  • Everything about the four points is completely dynamic, representing four different measures:
    • A measure for the minimum point
    • A measure for the maximum point
    • A measure for the target value
    • And a measure for the actual performance

So, anything that works for the card will work here as well. Starting with the X-axis, which consists of a table that ranges from the minimum to the maximum, these can be measures, and everything related to the design of the points. The logic I outlined in the attached file should be able to apply to any measure you create.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

Hi @Ritaf1983 - thanks again for you reply.

 

I've taken another look and tried making it work based on what you pointed out however I still can't seem to replicate it. It seems only the first measure pulls through onto the visual and it seems to be showing a number of values, rather than the 4 I want to show

 

JoshP11_0-1728903334209.png

 

JoshP11_2-1728903392556.png

 

 

JoshP11_1-1728903362035.png

 

Is there anything based on the screenshots I could be missing? I also did the colour but nothing appears for that as well.

 

JoshP11_3-1728903620686.png

 

 

Thanks,

Josh

 

Hi @JoshP11 
i see here some circular dependency...

Ritaf1983_0-1728904386685.png

Try to follow my steps as is / share the link for the pbix

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

Hi @Ritaf1983 

 

I tried following your steps as is, but I need a way of getting the minimum and maximum value from a column that has 100s of rows. I seen in your solution the min, max, and current were in one table with just one row, so it looks like you was just calling to one column which has one value? Or have i misunderstood?

Is there a way of doing this differently, so I can get the min and max from either the original data table or from the generateseries i followed in your step?

 

JoshP11_2-1728911637765.png

 

 

JoshP11_0-1728911435127.png

 

Unfortunately I'm unable to send the file and it would be difficult to re-create, I appreciate this makes it harder to provide any help!

 

 

Hi @JoshP11 

I’m sorry, but I’m struggling to understand what you’re trying to achieve.

The X-axis is a range of values, so there is a column there. The measures you're comparing (start, end, target) are scalar measures.

At no point am I comparing the axis to itself, rather I’m comparing the measure to the value at its point on the axis.

I don’t know how else I can help without a tangible file to demonstrate on.

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

@Ritaf1983 - no problem! 

Essentially there is rows and rows of data with results in them of different test types. An example could be a measurement of the left calf muscle, and then another of the right calf muscle, then a column known as Calf Swing.

 

This is the calculation for how much of a swing in size difference there is. The taget is always for these measurements (left and right) to be pretty much the same, hence the target of the calculation is better the closer to 0.

 

I have a measure that gets me the minimum value (of all known measurements), the max (of all known measurements), and then the actual (the measure that calculates the percentage swing, based off the selected value in my slicers). I'm trying to plot them in a visual.

Thank you for your help - i totally understand why you'd need to file to help further, but thanks again!

 

Hi @JoshP11 

I apologize, but one of the reasons I love data visualization so much is that I have a very visual way of understanding things. When it comes to logical questions, I need to “feel” the data.

That’s why my response included screenshots and a file to follow along with. This is also the reason I ask for files in such questions—I simply can’t understand or solve these kinds of issues without seeing and practicing the steps.

Unfortunately, I won’t be able to assist further without a file.

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile
ray_aramburo
Super User
Super User

I'd recommend Gauge Visual, you can conditionally format the color of progress as well 🙂 

ray_aramburo_0-1728597732978.png

ray_aramburo_1-1728597762164.png

 

 





Did I answer your question? Give your kudos and mark my post as a solution!

Proud to be a Super User!





Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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