Dynamic change in X Axis

[bleaf]

Hi 

I have a bar chart including each company sales (Table1) and benchmark sales (Table2). The benchmark calculation is a measure of average sales based on the selection of Category of companies. For example Category 1 includes Company A, B, C. Please see the following chart:

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: 


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]

@bleaf,

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

[bleaf]

Thank you. It's not empty column value, it's an issue on Measure vs Column Value showing in the combo charts. Only measure can calculate dynamic benchmark based on 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. 

[v-chuncz-msft]

@bleaf,

Please share us a simplified model.

[bleaf]

Hi,

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".

When the category slicer is selected as indiviaul item, the benchmark red line should be average sales of companies within specific category. 

 

 In order to create benchmark combo chart, I copied the Sales table into SalesBenchmark table and created a Categoryslicer 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 :

The two measures DAX are shown as below:
1. CategorySlicer = IF(HASONEVALUE(Sales[Category]),VALUES(Sales[Category]))
2. 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])))
RETURN

IF(HASONEVALUE(Sales[Category]),AverageSalesPerCategory,AverageSalesCrossAllCategory)

Not sure what is wrong. Any inputs are high appreciated!

[v-chuncz-msft]

@bleaf,

Try to add ALLSELECTED Function to define a filter for CALCULATE Function.

[bleaf]

Thank you for inputs. It's same result after I added ALLSELECTED the measure:

SalesBenchmark =
VAR AverageSalesPerCategory = IF(HASONEVALUE(Sales[Category]),CALCULATE(AVERAGE(SalesBenchmark[Sales]),FILTER(ALLSELECTED(SalesBenchmark),SalesBenchmark[Category]=[CategorySlicer])))
VAR AverageSalesCrossAllCategory = CALCULATE(DIVIDE(SUM(SalesBenchmark[Sales]),DISTINCTCOUNT(SalesBenchmark[Company])))
RETURN
IF(HASONEVALUE(Sales[Category]),AverageSalesPerCategory,AverageSalesCrossAllCategory)

[v-chuncz-msft]

@bleaf,

Try the second calculate function.

[bleaf]

Still same after I changded the second calculate in variable. I guess I have to recreate data model to make it works. Thank you.

[Ashish_Mathur]

hi @bleaf,

Share the link from where i can download your file.

[bleaf]

Hi Ashish,

Thank you very much. I am going to try different approach to solve this issue. Thanks.