one value on secondary axis

[itsmeanuj]

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

[nandic]

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

[itsmeanuj]

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

[amitchandak · Accepted solution]

@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