Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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
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
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?
Can you share the data model and sample data and the output desired to explore.
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
Hi @Adil
Please check out
http://powerbi.tips/2016/05/measures-calculate-cagr/
I am sure it will help.
Cheers
CheenuSing
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
Hi @Adil
Can you share the pbix file in one dirve or google drive and post the link to check out.
Cheers
CheenuSing
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!
Auto is the only option and it will work automatically based on the result set. You can't set the Axis values dynamically.
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |