bar graph with dynamic x axis shows max

[Pikachu-Power]

Hello 🙂

I have the following situation:

When I change this table to a bar graph the maximum of the x axis is automatic 20. In an another table it is 50. I want that this table also gets the max = 50 in the x-axis in a dynamic form. I tried some measures but the used filters in the filter pane makes it not easy. some ideas?

[MFelix]

Hi @Pikachu-Power ,

You can setup the MAX and MIN of an axis to a defined value however to what I can understand you want it to have it dinamically.

One workaround is to use a combo chart with line and bar chart, then make a measure to use on the line with the values you need from the second table.

Then make a single Y-axis for line and bar chart and make the line with size 0 and that will give you an automatic maximum for the axis.

I made a simple example with the following measure:

Line Value = MAXX(ALLSELECTED('Table (2)'[Cat]);CALCULATE(SUM('Table (2)'[Column1]) ))

Has you can see on the image below the two top chart are the same however the in the second one I made the size of the line to 0 (so it's your final result), as you can see the axis changes accordingly to the selected categories on the bottom chart.

This example I don't change the categories on the first chart but everthing will be dinamic.

If you want you can make a different approach that is to have a measure that make the max between both tables and use that as you maximum value on both charts second page on the PBIX file:

Line Value_ BOTH = 

VAR TABLE2_MAX =MAXX(ALLSELECTED('Table (2)'[Cat]);CALCULATE(SUM('Table (2)'[Column1]) ))
VAR TABLE1_MAX =  MAXX(ALLSELECTED('Table'[Categorie]);CALCULATE(SUM('Table'[Number]) ))
RETURN
IF(TABLE1_MAX> TABLE2_MAX; TABLE1_MAX; TABLE2_MAX)

On this second ones I didn't made the line to 0 just for visualization explanation you should do that and also hide the legend.

Check PBIX file attach.

[Pikachu-Power]

I forgot to mention that I use the horizontal bar graph which doesnt exist with lines.

[MFelix]

Hi @Pikachu-Power 

No problem just use the Stacked bar chart (1st visual) and created this two measures:

Line Value_ BOTH_Table 1 = 

VAR TABLE2_MAX =MAXX(ALLSELECTED('Table (2)'[Cat]);CALCULATE(SUM('Table (2)'[Column1]) ))
VAR TABLE1_MAX =  MAXX(ALLSELECTED('Table'[Categorie]);CALCULATE(SUM('Table'[Number]) ))
RETURN
IF(TABLE1_MAX> TABLE2_MAX; TABLE1_MAX- SUM('Table'[Number]); TABLE2_MAX- SUM('Table'[Number]))

Line Value_ BOTH_Table 2 = 

VAR TABLE2_MAX =MAXX(ALLSELECTED('Table (2)'[Cat]);CALCULATE(SUM('Table (2)'[Column1]) ))
VAR TABLE1_MAX =  MAXX(ALLSELECTED('Table'[Categorie]);CALCULATE(SUM('Table'[Number]) ))
RETURN
IF(TABLE1_MAX> TABLE2_MAX; TABLE1_MAX- SUM('Table (2)'[Column1]); TABLE2_MAX- SUM('Table (2)'[Column1]))

Add the each measure to the corresponding table chart the the final result will be as show below:

I keeped the Blue dark for you to see the result you need to put in the same colour as your chart as you see on the bottom charts.

Check PBIX file attach with example.

[Pikachu-Power]

I use the stacked bar chart grouped (3rd visual). Sorry.

[MFelix]

Hi @Pikachu-Power ,

If the information you want to present on the chart is only the one that you show on the table and want a dinamic max the solution is to use the first chart.

If this is not matching your requirements you need to give a little bit more insight on what is your data, how you want to show it in the graph and how is the datamodel relationship.

Sorry but without that information I will keep guessing the end result and will not be abble to help you effectively.

Can you share a sample file and expected result.

[Pikachu-Power]

Ok I understand and could implement it. Good idea. But this solution is bad for data labeling. Only middle presentation works. outside bar graph doesnt exit and at the end of the bar graph the labeling is cut off. is a better implementation plannend?

I use two different visuals (with different categories) with one table as a source. would it be in that way easier to implement?

[MFelix]

Hi @Pikachu-Power ,

I just pasted in some values on two tables and made two measure based on the information you have and giving you some options.

Sorry once again, and this is my fault for sure but Idon't understand what  do you want to achieve and how?

DInamic axis are not possible without the use of measures and making adjustments on current on the way the axis, lines, values etc are presented.

If you can explain or show exactly what is the final outcome you want to have and the data source would be a huge help.