Forum Discussion
Helper IVone value on secondary axis
Hi, I have sales of 3 products as shown below in the table. I want to create a line chart in PBI, however, sales of Product C is so high which shows the sales of the other 2 product almost 0 on the line chart which is not true. Is it possible to show product C on the secondary axis and remaining products on the primary axis keeping the legends intact?
| Product | Month | Sales |
| A | 1-Jan | 100 |
| B | 1-Jan | 90 |
| C | 1-Jan | 10000 |
| A | 1-Feb | 110 |
| B | 1-Feb | 105 |
| C | 1-Feb | 11000 |
| A | 1-Mar | 120 |
| B | 1-Mar | 110 |
| C | 1-Mar | 14000 |
| A | 1-Apr | 130 |
| B | 1-Apr | 95 |
| C | 1-Apr | 18000 |
| A | 1-May | 125 |
| B | 1-May | 130 |
| C | 1-May | 17000 |
| A | 1-Jun | 110 |
| B | 1-Jun | 115 |
| C | 1-Jun | 16000 |
itsmeanuj , if you plan to use the legend, it will show all one axis. You can create a measure filter with product C and use that on secondary axis
4 Replies
- amitchandak
Super User
itsmeanuj , if you plan to use the legend, it will show all one axis. You can create a measure filter with product C and use that on secondary axis
- nandic
Resident Rockstar
Hi itsmeanuj ,
As amitchandak mentioned, in order to use one measure as column, second measure as line, it will display in legend these two measures. You can always play with measure names if needed 🙂
Sales Product C = CALCULATE(SUM(Sheet1[Sales]),FILTER(Sheet1,Sheet1[Product]="C"))
Sales Products AB = CALCULATE(SUM(Sheet1[Sales]),FILTER(Sheet1,Sheet1[Product] in {"A","B"}))Cheers,
Nemanja
itsmeanujnandic amitchandak - With this approach, I will not be able to show individual products in legend. My data has 10 products out of which one product's sales are way too high to put it on a similar axis with other products. I want to show all the products in legend and product with very high sales on the secondary axis.
- nandic
Hi itsmeanuj ,
There must be better solution, but here is some kind of alternative.
You create 2 new columns in fact table: one returns sales amount only if product <> product C, second column returns sales amount only if product = product C. (if product C has much higer value comparing to other products)
You create 2 charts - one displays first column, second displays second column (this way you get all products on both charts, but chart columns are displayed only if sales amount > 0).
You need to configure some options on second chart (background transparent 100 %, remove title, axis x title, set y axis right).
Last step is to set second chart over first chart.
Note: this way you will not be able to click on both charts to filter other objects on the report, only on the one which is on top.
Screenshots:Cheers,
Nemanja
