Rank Visual over time

[PaulDBrown]

Hi community,

I am hoping someone may suggest a better visual representation of a rank over time. This is what I have so far:

Ideally I would like the line to be the inverse: ie. I'm trying to show that being ranked closer to 1 is better; now it looks like the higher number the rank is the better. 

Any suggestions?

Thanks.

[az38]

Hi @PaulDBrown 

multiply rank to -1 🙂

do not hesitate to give a kudo to useful posts and mark solutions as solution
Linkedin

[PaulDBrown]

@az38 

Thanks for the suggestion.. I did consider that option but would rather avoid displaying negative values in the labels.

Do you know a way of avoiding this?

[v-lili6-msft]

hi @PaulDBrown 

There is currently no option to invert the Y axis in your chart.

Here are two workaround for you refer to:

1.  change the ASC to DESC in your rank measure

RANKX ( <Table>, <Expression> [, <Value>] [, <Order>] [, <Ties>] )

Order

Optional

The order to be applied. 0/FALSE/DESC – descending; 1/TRUE/ASC – ascending.

2. To make the graph look positive , we can make a measure for the values and then multiply it with -1.

Then download a "multiple axis chart - XViz" and drag your measure into the values section. This will display an inverted chart but will show the data as negative values.

To change this go to number format and turn on semantic formatting.

Change the negative value format and positive value format to (X). You can change their colours as well. This will now show the values without the negative sign.

of cource, you could disable Y-axis for this visual.

for example:

Regards,

Lin

[PaulDBrown]

@v-lili6-msft 

Thank you for your suggestions. The custom visual looked very promising but...

In the report I'm working on, the visual will be filtered when a row is selected in a table (if no row is selected, then the visual is blank)

For some reason the measure I'm using to display data when filtered is not working in the custom visual (it works if the selection is made with a slicer, but doesn't work if the selection is made with a row selection in a table).

It does however work with Power BI defualt visuals.

The measure itself is:

Rank x negative if filtered = 
VAR calc = CALCULATE(COUNTROWS('Sales Products'); ALLSELECTED('Sales Products'[Product]))
Return
IF(calc = 1; [Rankx negative]; BLANK())

'Sales Product' is a lookup table; 'Sales Product'[Product] is the field linked in a one-to-many relationship with the product column in the sales table. 'Sales Product'[Product] (from the lookup table) is the field used in slicers, tables and visuals.

Here are 3 screenshots - The visual I'm looking for is the "Multiple Axes Chart Custom Visual" on the bottom right of each screenshot

1) No selection in either slicer or table: bottom right visual shows no values = correct

2) Sales Product selected in Slicer: all visuals are correct

c) Selection made in table row: Power BI default visuals display correctly; bottom right custom visual remains blank 

I have tried other functions (ISCROSSFILTERED for example) and I can't get it to work...

Any ideas why this is happening or how to solve it?

Thanks again!

[v-lili6-msft]

hi @PaulDBrown 

It works wellon my side, If possible, please share your sample pbix file for us have a test.

Regards,

Lin

[PaulDBrown]

@v-lili6-msft 

Good evening Lin, and thank you for offering to check this out.

Here is the file: PBIX file 

[v-lili6-msft]

hi @PaulDBrown 

The problem is that the interaction is "Highlight" for this custopm visual default in your report.

Just change it to "Filter", then it will work well.

https://docs.microsoft.com/en-us/power-bi/service-reports-visual-interactions

Result:

BeforeAfter

Regards,

Lin

[PaulDBrown]

@v-lili6-msft 

Good morning Lin. I can't believe I overlooked the interaction behaviour!!! (Now I feel really silly....)

Thanks again!!

[PaulDBrown · Accepted solution]

@v-lili6-msft @az38 

Just an update on this theme of "inverting"  Y Axis and the relevant workarounds for the benefit of others who might be looking for a similar solution.

The premise to the solution is to convert the rank number to a negative value by mutltiplying the rank * -1, and use this in a line visual. (The -1 will  appear higher, since it is a larger value, while - 15 will be below). The problem is that displaying a negative value in the labels is clumsy and counter-intuitive for the end user of the report.

I did initially use the custom visual "Multiple Axes Chart" as suggested by Lin in this thread, but came across licensing issues and the fact that for some reason the label format in the chart seems to revert to default (with decimals) when published to my Workspace.

To cut a long story short, I actually had a bit of a revelation when I remembered that we can actually create custom formats for measures etc in Power Bi since "recently" (I did actually look for this originally but couldn't find the way to custom format numbers in the usual "Measure Tools" or visual formatting pane). 

And yet it is available, albeit somewhat hidden, by going into the modeling section in Desktop, selecting the measure and "Fields" list and going into properties. This is where you CAN create your custom number format.

So I formatted my negative numbers to absolute number (got rid of the "-" symbol) and voilà!

So I can now use the default Line Chart visual to create my rank trend (with the stepped layout which I think is perfect for a rank trend: it resembles a podium) with the best rank on top/ worst rank below and without displaying the rank as a "negative" number. Just what I was looking for.

"Negative" numbers used to display rank

Hope this might be useful for others!