Change Y-Axis Format field based on a slicer or data value

[JohnAdv]

Hi Fellow PBI explorers,

Simply, i want to change the Y-axis Start and End when i click on a slicer choice.

Generally, Power BI at this point appears to not allow us to change Format values (such as Axis Start/End,

Data colors) dynamically based on data values. (I haven't explored custom visual development yet.)

I need to have a line or bar chart change the Start and End values for the Y-axis based on data  in a table or slicer.

In other ords, dynamically change the Y-axis scale.

one value on a slicer will show chart values between 0 and 1 while another option on the slicer will cause

the chart to show values between 0 and 100.

John

[Anonymous · Accepted solution]

@JohnAdv

I am assuming what you want to show on the y-axis is different measures depending on the selection in slicer. 

1. Let us assume you have a SalesTransaction table that has both Quantity and SalesValue.

2. We have a slicer that displays Quantity and SalesValue as a selection.

3. Based on the selectionn in the slicer we want to display Quantity or SalesValue in the Y-Axis of the graph.

4. To acheive the above follow the steps below.

5. Create a static table called SelectMeasure  ( not connected to anyother table in the model)  having columns 

     MeasureID and MeasureName

6. Rows for the example will be

      1. Quantity

      2. SalesValue

7.  Create a slicer using the MeasureName. 

8.  Create two measures called TotalQuantity = Sum(Quantity) and TotalValue = Sum(SalesValue)

9.   Create a measure called DisplayMeasure as

      DisplayMeasure= switch (TRUE,
                                                     Min ( 'SelectMeasure'[MeasureID] ) = 1,
                                                                      ( [TotalQuantity] ),
                                                     Min ( 'SelectMeasure'[MeasureID] ) = 2,
                                                   ( [TotalValue] ) )

10. In your chart use this DisplayMeasure as the Value column.

11. Depending on the selection in the slicer the Y-axis will change according to the values of Quantity or SalesValue.

Try this and if this works please accept it as a solution and also give Kudos.

Cheers

Cheeusing

[Anonymous · Accepted solution]

@JohnAdv

I am assuming what you want to show on the y-axis is different measures depending on the selection in slicer. 

1. Let us assume you have a SalesTransaction table that has both Quantity and SalesValue.

2. We have a slicer that displays Quantity and SalesValue as a selection.

3. Based on the selectionn in the slicer we want to display Quantity or SalesValue in the Y-Axis of the graph.

4. To acheive the above follow the steps below.

5. Create a static table called SelectMeasure  ( not connected to anyother table in the model)  having columns 

     MeasureID and MeasureName

6. Rows for the example will be

      1. Quantity

      2. SalesValue

7.  Create a slicer using the MeasureName. 

8.  Create two measures called TotalQuantity = Sum(Quantity) and TotalValue = Sum(SalesValue)

9.   Create a measure called DisplayMeasure as

      DisplayMeasure= switch (TRUE,
                                                     Min ( 'SelectMeasure'[MeasureID] ) = 1,
                                                                      ( [TotalQuantity] ),
                                                     Min ( 'SelectMeasure'[MeasureID] ) = 2,
                                                   ( [TotalValue] ) )

10. In your chart use this DisplayMeasure as the Value column.

11. Depending on the selection in the slicer the Y-axis will change according to the values of Quantity or SalesValue.

Try this and if this works please accept it as a solution and also give Kudos.

Cheers

Cheeusing

[padodge]

I have tried several variations of this approach with MeasureID being in one table and Quantity being in an unrelated table (within a Tabular model).  

The calculated column (measure) does not fail, but the slicer on MeasureID has no affect in Power BI.  In other words, the value returned is always [TotalQuantity], even when MeasureID=2 is selected in the slicer.  Am I missing something?  Is it possible to slice a value from an unrelated table?

[Anonymous]

@padodge

Can you share the data model and sample data and the output desired  to explore.

@Anonymous

[Adil]

Dear Cheenu Sing

I wonder if you can help in solving the issue I have with the Y Axis in this scatter chart with slicers.

based on date selection I need to compute the CAGR for the consecutive years based on the first year in the record that hase been selected in ther slicer. so if I select 2010 through 2015 years the Y axis should adjust automatically and calculate the CAGRs for this period.

CAGR = (category value last year selected/ category value first year selected) ^ (1/(last year selected-first year selected))-1

Thank you in advance

[Anonymous]

Hi @Adil

Please check out 

http://powerbi.tips/2016/05/measures-calculate-cagr/

I am sure it will help.

Cheers

CheenuSing

[Adil]

Thank you @Anonymous

I looked to the CAGR post before to calculate each category CAGR as a measure (CAGR Cat_1, CAGR Cat_2, CAGR Cat_3), my problem is that in scatter chart Y axis you need one value for all categories based on the slicer by year (i.e if i select multiple years, the Y axis should present values of CAGR for the first to the last year selected for all categories).

thanks

Adil

[Anonymous]

Hi @Adil

Can you share the pbix file in one dirve or google drive and post the link to check out.

Cheers

CheenuSing

[Adil]

here is the link for the file

https://1drv.ms/u/s!AtTB3YAq-_HrgWJL7UQPsEzIVwkX

I put the CAGR results for each category FYI. 

[JohnAdv · Accepted solution]

I like it, Thanks!

[Habib]

Auto is the only option and it will work automatically based on the result set. You can't set the Axis values dynamically.