Forum Discussion

bleaf's avatar
bleaf
Regular Visitor
8 years ago

Dynamic change in X Axis

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. Fo...
Need Help
bleaf's avatar
bleaf
Regular Visitor
8 years ago

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's avatar
v-chuncz-msft
Icon for Community Support rankCommunity Support
8 years ago

bleaf,

 

Please share us a simplified model.

  • bleaf's avatar
    bleaf
    Regular Visitor
    8 years ago

    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!

      • bleaf's avatar
        bleaf
        Regular Visitor
        8 years ago

        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)