How to set separate Y-Axis ranges for single-column small multiples chart?

[Anonymous]

I am looking to make a small multiples chart across four sales categories that have very different ranges. I'd like to avoid breaking these into separate charts, but I can't figure out a way to make the Y-axis relative to the range for each dimension.

I've created a measure that accurately returns the MAX value along year for each of these categories, but that does not return the correct axis result. The formula is of the format: 

Max Across Years = CALCULATE( MAXX( SUMMARIZE( 'Table', 'Table'[Date].[Year], "Annual Amount", SUM( 'Table'[Value] )), [Annual Amount] ), ALLEXCEPT('Table', 'Table'[Metric Name]) )

[amitchandak]

@Anonymous , You can try small multiple for this , with setting 1 column and 3 rows

https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-small-multiples

[Anonymous]

Thanks @amitchandak. I am using that visual with that configuration, but each Y-axis is set to the same range. Should this automatically set independent axis ranges for each dimension used for small multiples? 

If that is not automatic, do you know how I can use a formula to properly set the end of the axis to be relative to the max value along each row in the chart image I pasted above? 

[v-kelly-msft]

Hi  @Anonymous ,

You could click the setting button on the right upper corner>scale>choose "Individual Scale":

And you will see the different Y-axis range:

Check below link for reference:

https://xviz.com/blogs/advanced-trellis-small-multiples-key-features-power-bi-visual/

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[Anonymous]

Hi @v-kelly-msft, thank you! 

I should have specified that I am using the Small Multiples option for the stacked column chart visual. Is this possible using this visual? 

I do plan to use this with PowerBI Service and would like to be able to configure this dynamic axis without paying for the specific visual. 

[v-kelly-msft]

Hi  @Anonymous ,

Yes,based on my test,it can.For more details ,you could check the reference I show.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[Anonymous]

Thank you! I don't see that same menu that you show in PowerBI desktop.

And the reference you posted is from the specific xViz Advanced Trellis chart, which is not the default PowerBI stacked column chart.

These are the only options I have for the default stacked column visual. 

I do have an option to make a formula-based start and end to the axis, but my formula that I shared above does not work when I set that as the End of the axis: 

[v-kelly-msft]

Hi @Anonymous ,

Sorry for misunderstanding.

Function for small multiples in clustered column chart is very limited, all the settings for Y-axis can only be synchronized.

How about using the visual I recommended before?

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[Anonymous]

Hi Kelly, 

Thanks! Using the xViz option could work, but I do plan to use this in the PowerBI Service, which is an additional cost. 

This is helpful, as this seems like a pretty basic requirement for small multiples and should be added to the column charts where small multiples is an option. To expect every partition of the small multiples to have similar data ranges seems strange to me, though I get that having a uniform axis may be desirable for many data profiling scenarios.

[v-kelly-msft]

Hi @Anonymous ,

Maybe that's why xViz Advanced Trellis chart is created,clustered column chart is more forcused on vertical visualization which is compared to bar chart,and small multiples is not its key feature.

Best Regards,
Kelly

Did I answer your question? Mark my raeply as a solution!