cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JohnAdv Frequent Visitor
Frequent Visitor

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

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
CheenuSing Super Contributor
Super Contributor

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

@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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
JohnAdv Frequent Visitor
Frequent Visitor

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

I like it, Thanks!

10 REPLIES 10
Habib Established Member
Established Member

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

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

 

 

CheenuSing Super Contributor
Super Contributor

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

@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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
JohnAdv Frequent Visitor
Frequent Visitor

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

I like it, Thanks!

padodge Frequent Visitor
Frequent Visitor

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

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?

CheenuSing Super Contributor
Super Contributor

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

@padodge

 

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

 

@CheenuSing

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Adil Frequent Visitor
Frequent Visitor

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

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

 

Picture1.png

 

Thank you in advance

CheenuSing Super Contributor
Super Contributor

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

Hi @Adil

 

Please check out 

 

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

 

I am sure it will help.

 

Cheers

 

CheenuSing

 

 

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Adil Frequent Visitor
Frequent Visitor

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

Thank you @CheenuSing

 

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

CheenuSing Super Contributor
Super Contributor

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

Hi @Adil

 

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

 

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 375 members 3,092 guests
Please welcome our newest community members: