Dynamic change in X Axis

Question

Hi&nbsp;I have a bar chart including each&nbsp;company sales (Table1) and benchmark sales (Table2). The benchmark calculation is a measure of average sales based on&nbsp;the selection of Category of companies. For example Category 1 includes Company A, B, C. Please see the following chart:&nbsp;When I chose Category 2 in slicer, the bechmark and the related companies would change. This time the Category 2 includes only company A and C. The chart changes as below:&nbsp;Is there any way that I can set the X-Axis to a daynamic way to show the chart as below instead ?The tricky part is that company sales and company names (X-Axis) are located in columns, while the benchmark is a measure from another table. So it would be hard to make X - Axis dynamically. Any inputs are highly appreciated !

v-chuncz-msft · Answer

bleaf,
&nbsp;
A complete example might be better. You may first check Unpivot Columns in Query Editor and Show Categories With No Data.

bleaf · Answer

Thank you. It's not empty column value, it's&nbsp;an issue on&nbsp;Measure vs Column Value showing in the combo charts. Only measure can calculate dynamic benchmark based on&nbsp;slicer result of the category. While sales column is compatible with company name column as X-Axis, sales benchmark measure is not. So when the company name is selected as X-Axis, the measure's company name X-Axis is not dynamic.&nbsp;

v-chuncz-msft · Answer

bleaf,
&nbsp;
Please share us a simplified model.

bleaf · Answer

Hi,&nbsp;I tried to use a simplified model, however it shows wrong benchmark line. The benchmark red line should be a straight line to calculate the average sales across all the categories of companies when the category slicer is selected as "ALL".&nbsp;&nbsp;When the category slicer is selected as indiviaul item,&nbsp;the benchmark red line should be average sales of companies within specific category.&nbsp;&nbsp;&nbsp;&nbsp;In order to create benchmark combo chart, I copied the Sales table into SalesBenchmark table and&nbsp;created a Categoryslicer&nbsp;measure to hold the selected value for Category slicer. Then used SalesBenchmark to calculate average sales cross the companies. The two table has no relationship. Sales[Category] column is selected as Category slicer.The two tables columns are listed as below :&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;The two measures DAX are shown as below:1. CategorySlicer = IF(HASONEVALUE(Sales[Category]),VALUES(Sales[Category]))2.&nbsp;SalesBenchmark =VAR AverageSalesPerCategory = IF(HASONEVALUE(Sales[Category]),CALCULATE(AVERAGE(SalesBenchmark[Sales]),FILTER(SalesBenchmark,SalesBenchmark[Category]=[CategorySlicer])))VAR AverageSalesCrossAllCategory = CALCULATE(DIVIDE(SUM(SalesBenchmark[Sales]),DISTINCTCOUNT(SalesBenchmark[Company])))RETURNIF(HASONEVALUE(Sales[Category]),AverageSalesPerCategory,AverageSalesCrossAllCategory)Not sure what is wrong. Any inputs are high appreciated!

v-chuncz-msft · Answer

bleaf,
&nbsp;
Try to add ALLSELECTED Function&nbsp;to define a filter for CALCULATE Function.

Forum Discussion

Dynamic change in X Axis

10 Replies

Related content

Dynamic Axis Relabelling

dynamic x-axis based on filter

Dynamic change in X Axis

Change the x-axis dynamically

dynamic x axis