cancel
Showing results for
Did you mean:

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

Regular Visitor

## Showing table with python containing trendline formula from scatter plot

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
New Member

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

Super User

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

Super User

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

Regular Visitor

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

#### Europe’s largest Microsoft Fabric Community Conference

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

#### Power BI Monthly Update - August 2024

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

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

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

Top Solution Authors
Top Kudoed Authors