Forum Discussion
Change Y-Axis Format field based on a slicer or data value
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 like it, Thanks!
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 like it, Thanks!
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?
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
