Forum Discussion
Dynamic change in X Axis
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!
- bleaf8 years agoRegular Visitor
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-msft8 years agoCommunity Support
- bleaf8 years agoRegular Visitor
Still same after I changded the second calculate in variable. I guess I have to recreate data model to make it works. Thank you.