The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.

**Save €200 with code MSCUST on top of early bird pricing!**

- Power BI forums
- Updates
- News & Announcements
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Custom Visuals Development Discussion
- Health and Life Sciences
- Power BI Spanish forums
- Translated Spanish Desktop
- Training and Consulting
- Instructor Led Training
- Dashboard in a Day for Women, by Women
- Galleries
- Community Connections & How-To Videos
- COVID-19 Data Stories Gallery
- Themes Gallery
- Data Stories Gallery
- R Script Showcase
- Webinars and Video Gallery
- Quick Measures Gallery
- 2021 MSBizAppsSummit Gallery
- 2020 MSBizAppsSummit Gallery
- 2019 MSBizAppsSummit Gallery
- Events
- Ideas
- Custom Visuals Ideas
- Issues
- Issues
- Events
- Upcoming Events
- Community Blog
- Power BI Community Blog
- Custom Visuals Community Blog
- Power BI 中文博客
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

- Power BI forums
- Forums
- Get Help with Power BI
- Desktop
- Re: Showing table with python containing trendline...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Showing table with python containing trendline formula from scatter plot

05-03-2023
02:11 AM

I have a Python script running in Power BI desktop that shows a scatter plot with a exponential and linear trendline. Also it shows the formula of the trendlines and the R-squared value. My dataset contains sales by temperature per product. Each product is part of a segment. If i select a segment in a Power BI slicer i get the scatter plot and trendline formulas and R-squared values for that specific segment.

But i want to see the trendline formula and R-squared value per segment in a table. So i can instantly see all the segments and their corresponding values. I have the following code for the scatter plot:

```
# The following code to create a dataframe and remove duplicated rows is always executed and acts as a preamble for your script:
# dataset = pandas.DataFrame(average sales per day, temperature_2m_max °C)
# dataset = dataset.drop_duplicates()
# Paste or type your script code here:
import matplotlib.pyplot as plt
import pandas as pd
import numpy as np
# Extract the x and y values from the dataset
x = dataset['temperature_2m_max °C']
y = dataset['average sales per day']
# Fit the data to an exponential curve
p = np.polyfit(x, np.log(y), deg=1)
a = np.exp(p[1])
b = p[0]
# Calculate the predicted y values
y_pred_exp = a * np.exp(b * x)
# Fit the data to a linear trendline
slope, intercept = np.polyfit(x, y, 1)
y_pred_lin = slope * x + intercept
# Calculate the R-squared value for the exponential trendline
ss_tot = np.sum((y - np.mean(y))**2)
ss_res_exp = np.sum((y - y_pred_exp)**2)
r_squared_exp = 1 - (ss_res_exp / ss_tot)
# Calculate the R-squared value for the linear trendline
ss_res_lin = np.sum((y - y_pred_lin)**2)
r_squared_lin = 1 - (ss_res_lin / ss_tot)
# Plot the data and the trendlines
fig, ax = plt.subplots(figsize=(8, 5))
ax.scatter(x, y, color='black')
ax.plot(x, y_pred_exp, color='green', label='Exponential Trendline')
ax.plot(x, y_pred_lin, color='blue', label='Linear Trendline')
ax.text(0.01, 0.8, f'Exponential Trendline: y = {a:.2f} * exp({b:.3f}x), R-squared: {r_squared_exp:.4f}', transform=ax.transAxes)
ax.text(0.01, 0.7, f'Linear Trendline: y = {slope:.2f}x + {intercept:.2f}, R-squared: {r_squared_lin:.4f}', transform=ax.transAxes)
ax.legend()
plt.show()
```

I can't figure out what the code should be for displaying a table with the values i want to see. The table should look something like this:

- Column A: name of segment
- Column B: linear trendline formula
- Column C: linear R-squared value
- Column D : exponential trendline formula
- Column E: exponential R-squared value

Can someone help me with this?

@python @pythonia

4 REPLIES 4

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

03-26-2024
12:37 PM

In your Python script, how do you refer to actual columns/data already loaded in your pbix?

I have loaded some data from SQL and I cant find a way to use them as df

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

03-26-2024
12:51 PM

you add the desired columns to the values area of the Python visual. That will automatically include them in the dataframe.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-06-2023
06:48 PM

The Python visual MUST plot something. If you want a table, plot a table. pandas.plotting.table — pandas 2.0.1 documentation (pydata.org)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-09-2023
12:15 AM

Hi Ibendlin. Thanks this points me in the right direction but not exactly what i wanted. I created the table below. But i want to move the exponential en linear trendline to the columns and on the rows i want the different segments.

What i have now:

What i want:

Segment | Exponential formula | Linear formula | Exponential R-squared | Linear R-squared |

Segment 1 | ||||

Segment 2 |

I have a sample dataset in this onedrive link: https://1drv.ms/u/s!AotJBlCCCXxigpNv7xxhBpxVSxeUYQ?e=xY5PJh

I can't figure out this next step

Announcements

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Check out the August 2024 Power BI update to learn about new features.

Featured Topics

Top Solution Authors

User | Count |
---|---|

119 | |

89 | |

75 | |

53 | |

45 |

Top Kudoed Authors

User | Count |
---|---|

135 | |

120 | |

75 | |

65 | |

64 |