Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JohnAdv
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
CheenuSing
Community Champion
Community Champion

@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!

View solution in original post

I like it, Thanks!

View solution in original post

10 REPLIES 10
CheenuSing
Community Champion
Community Champion

@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!

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?

@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!

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
Community Champion
Community Champion

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!

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
Community Champion
Community Champion

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!

here is the link for the file

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

 

I put the CAGR results for each category FYI. 

I like it, Thanks!

Habib
Responsive Resident
Responsive Resident

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

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.