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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
jinny_le
Frequent Visitor

Display distance between 2 lines chart as a third line

hi PBI guru, 

I would like to seek help. I have daily sales data of 2 products. I want to display the daily sale of each product and also the sales difference between them in the same line chart. Anyone has an idea how do to it? Thank you so much! 

Capture 2.PNG

 

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

Hi @jinny_le 

If you'd like to show 2 products on a line chart at one time, you could add "product name" column into slicer, then select two products each time.

Or you could create tables and add columns to slicer.

1.JPG

product slicer1 = VALUES('Table'[product])

product slicer2 = VALUES('Table'[product])

Measure = CALCULATE(SUM('Table'[price]),FILTER('Table','Table'[product]=[Selected1]||'Table'[product]=[Selected2]))

 

Do you need to add another line to this line chart which shows the difference of price between two products?

Based on my experience, if you add columns to "legend" field of a line chart,

it is impossible to add more than one measure/column into "Values" field,

thus the line numbers showing one the visual are detemined by the legend.

 

Best Regards

Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

ddd

View solution in original post

5 REPLIES 5
v-juanli-msft
Community Support
Community Support

Hi @jinny_le 

If you'd like to show 2 products on a line chart at one time, you could add "product name" column into slicer, then select two products each time.

Or you could create tables and add columns to slicer.

1.JPG

product slicer1 = VALUES('Table'[product])

product slicer2 = VALUES('Table'[product])

Measure = CALCULATE(SUM('Table'[price]),FILTER('Table','Table'[product]=[Selected1]||'Table'[product]=[Selected2]))

 

Do you need to add another line to this line chart which shows the difference of price between two products?

Based on my experience, if you add columns to "legend" field of a line chart,

it is impossible to add more than one measure/column into "Values" field,

thus the line numbers showing one the visual are detemined by the legend.

 

Best Regards

Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

ddd

hi Maggie @v-juanli-msft 

Thanks for your detailed guiande. Yes i need to add the price-difference line and i understand the limitation you were talking about. But this output is good enough, so i'll just go ahead with this for now. Thank you 🙂 

 

amitchandak
Super User
Super User

For a difference you have to create a measure filtering each one of them

calculate(Avergae(table[price]),table[product]="BABA") -calculate(Avergae(table[price]),table[product]="JD")

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

hi @amitchandak , 

Thanks for your reply. If i have hundreds of product (but I only compare 2 of a time) - is there a more efficient way than listing each of the product and calculate the average for each of them? Thanks! 

@jinny_le ,

Assume you are selecting both in one slicer

Measure =
var _min = minx(Product,Product[Name])
var _max = maxx(Product,Product[Name])
return
calculate(Avergae(table[price]),Product[Name]=_min) -calculate(Avergae(table[price]),Product[Name]=_max)

 

Or

Measure =
var _min = minx(Product,Product[Name])
var _max = maxx(Product,Product[Name])
return
calculate(Avergae(table[price]),filter(all(Product),Product[Name]=_min)) -calculate(Avergae(table[price]),filter(all(Product),Product[Name]=_max))

 

The product is the dimension table for the product. If you do not have one create, otherwise this kind calculation will bring challenges with other filters.

 

if you need more help make me @

Appreciate your Kudos.

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

October NL Carousel

Fabric Community Update - October 2024

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