Forum Discussion
Advocate IIUse a measure an an axis (dynamic axis selection)
So, I am trying to create a dynamic column chart, looking at the distribution of employees salary. I would like to be able to give the user the ability to select their own salary intervals (e.g. 2k, 5k, 10k etc).
I have successfuly created a measure field to do the calculation, based on a slicer, using:
Interval = if(HASONEVALUE(tbl_PayInterval[Interval]),
roundup(sum('Basic Pay'[BASIC_PAY])/values(tbl_PayInterval[Interval]),0)*values(tbl_PayInterval[Interval]),0)
This rounds up the pay for each record in the table to the nearest selected interval (where tbl_PayInterval is my single-column table that feeds the slicer).
The problem is that I can't then use the Interval field as an x axis for a column chart (it won't let me)
I could use a calculated field as an axis, but I can't create the same results: it returns zeros based on the if(HASONEVALUE returning false.
Any suggestions, please? Thanks!
9 Replies
hohlickContinued Contributor
AFAIK from MattAllington, to use as axis you have to make calculated column, not measure
chhuThanks - I think that is the conclusion I came to. But, is it possible to create a calculated column that does what I want it to - ie calculate a value based on a slicer selection?
- Habib
Yes you can but for this you need to use additional table. Please refer to below link. This will give you some idea how to achieve this. This is for excel but you can easily conver this to Power BI :)
http://sqljason.com/2012/11/measure-selection-using-slicers-in.html
