Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Line chat with clustered column chart with category visual

Hi Community, i have attached one image...please guide me how to build this visual in powerbi. note: bar1 for current year sales.bar2 for previous year sales. line to show difference of sales.and...
Need Help
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Community,

Thanks for your prompt response, here is my table image : i have taken two year data.


Here is my python code and visual image :


python script :
import matplotlib.pyplot as plt
import numpy as np

# Data
years = ['2022', '2023', '2024']
categories = ['banana', 'mango', 'papaya']
CY_values = np.array([[10, 20, 30], [15, 25, 40], [20, 30, 35]])
PY_values = np.array([[5, 10, 20], [10, 15, 20], [10, 20, 25]])

# Compute sums for CY and PY
CY_sums = np.sum(CY_values, axis=0)
PY_sums = np.sum(PY_values, axis=0)

# Compute differences between CY and PY
diffrence1 = CY_sums
diffrence2 = PY_sums
diffrence3 = CY_sums - PY_sums

# Plotting
fig, ax = plt.subplots()

# Bar width
bar_width = 0.35

# Positions of the bars on the x-axis
r1 = np.arange(len(years))
r2 = [x + bar_width for x in r1]

# Define colors for each category
colors = ['yellow', 'green', 'blue']

# Create stacked bars for CY and PY
bottom_CY = np.zeros(len(years))
bottom_PY = np.zeros(len(years))

for i, category in enumerate(categories ) :
    bars1 = ax.bar(r1, CY_values[i], bottom=bottom_CY, color=colors[i], width=bar_width, label=f'CY {category}' if i == 0 else "")
    bars2 = ax.bar(r2, PY_values[i], bottom=bottom_PY, color=colors[i], width=bar_width, label=f'PY {category}' if i == 0 else "")
    bottom_CY += CY_values[i]
    bottom_PY += PY_values[i]

# Add sum values on top of the bars
for r, CY, PY in zip(r1, CY_sums, PY_sums ) :
    ax.text(r, CY + 1, f'{CY:.0f}', ha='center', va='bottom')
    ax.text(r + bar_width, PY + 1, f'{PY:.0f}', ha='center', va='bottom')

# Plot the differences as a line
ax.plot([r + bar_width / 20 for r in range(len(years))], diffrence1, color='yellow', marker='o', label='CY Total')
ax.plot([r + bar_width / 20 for r in range(len(years))], diffrence2, color='green', marker='p', label='PY Total')
ax.plot([r + bar_width / 20 for r in range(len(years))], diffrence3, color='blue', marker='d', label='Difference (CY - PY)')

# Adding labels
ax.set_xlabel('Years')
ax.set_ylabel('Values')
ax.set_title('Stacked Bar Chart with Percentage Values and Difference Line')
ax.set_xticks([r + bar_width / 2 for r in range(len(years))])
ax.set_xticklabels(years)

# Custom legend for categories
legend_labels = [plt.Rectangle((0, 0), 1, 1, fc=colors[i], edgecolor='none') for i in range(len(categories))]
ax.legend(legend_labels, categories, loc='upper left')

plt.show()


So, i want to build same visual using powerbi visual.
I try with Line & clustred Column chart to build same visual but it will not display 3 line's and category.
here is image :

 

Thanks
finaz
fahadqadir3's avatar
fahadqadir3
Icon for Solution Supplier rankSolution Supplier
2 years ago

Anonymous Review the attached screenshot and measures. For each month you want to present Current Year and Previous Year and Line as difference.

CY_Values = SUMX(FILTER('Table', YEAR('Table'[Date]) = YEAR(MAX('Date'[Date]))), 'Table'[Value])
PY_Values = SUMX(FILTER('Table', YEAR('Table'[Date]) = YEAR(MAX('Date'[Date]))-1), 'Table'[Value])

This is a by design behavior. In Stacked Column Chart, when you add multiple fields into value section, it is not allowed to add legend.

 

Hope it works.

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!! 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Fahadkadir,

    Thanks for your reply. but is there is any visual in powerbi to build same visual like this :


    Thanks 
    finaz