Use a measure an an axis (dynamic axis selection)

[chhu]

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!

[hohlick]

AFAIK from @MattAllington, to use as axis you have to make calculated column, not measure

[chhu]

Thanks - 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

[chhu]

Thanks for taking the time to respond - I'm not sure this works though. The essence is that the calculated measure, based on a slicer, can't be used as a axis, or a row/column header in a pivot.

I've pulled together what I've done to show how the data is structured and what works here: http://bit.ly/29a9NSX

Note - ideally I'd like a user to choose any interval, through a text imput box, but I know that's not possible, hence the slicer.

[Analyst_PowerBI]

Any update?

Is it still not possible to use a measure for an axis? Or to filter (e.g. date filter)  a calculated column?

[chhu]

Just to mention, I also tried creating additional columns: "nearest 2000", "nearest 5000", "nearest 10000" and using the switch function in another new column to select the correct values:

ColAxis = switch(HASONEVALUE(tbl_Interval[Interval_Key]),
MIN(tbl_Interval[Interval_Key])=1,BasicPay[Nearest_2k],
	min(tbl_Interval[Interval_Key])=2,BasicPay[Nearest_5k],
	min(tbl_Interval[Interval_Key])=3,BasicPay[Nearest_10k])

[chhu]

...cont

But this doesn't seem to work - the ColAxis column doesn't change when teh slicer value is changed.

[v-sihou-msft]

@chhu

If you want to place the calculated results as an x axis, you have to define a calculated column. You used the same DAX formula for the calculated column and it always returns zero, because the column results will not be affected by the Slicer. It is like column results are calculated before you select one interval value. So the “IF(HASONEVALUE)” will always return False.

As I understand it, you want the IntervalMsr results to be the data range in the x axis. And the IntervalMsr results is based on the Slicer selection. So the x axis will change based on the slicer value.

In this scenario, I suggest to create a new hierarchy and use the drill down function without a slicer. Please refer to following steps.

1. Create four columns with below formula. Replace 20000 with other values (e.g. 10000, 5000, 2000) for other columns.
   

   Interval_Of_20000 = 
   ROUNDUP (
       CALCULATE (
           SUM ( BasicPay[BAISC_PAY] ),
           ALLEXCEPT ( BasicPay, BasicPay[OrigCode], BasicPay[BAISC_PAY] )
       )
           / 20000,
       0
   )
   * 20000
   

   
2. Right click “Interval_Of_20000” and create a new hierarchy. Right click other three columns and select ‘Add to Interval_Of_20000 Hierarchy’.
   
3. Drag column chart into your canvas and change interval by “Drill” function.
   

[Anonymous]

How can I achieve this chart layout with the data below.

Data :

My chart is showing April data for all Team followed by each month.

Thanks,

Triparna