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 both bar are dispaly categories.

 


Thanks
finaz

Need Help

4 Replies

  • foodd's avatar
    foodd
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Hello Anonymous , and thank you for sharing a question with the Community.   The following is informational.  Please remember to adhere to the decorum of the Community Forum when asking a question.

    Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot). You can upload the PBIX file to a cloud storage service such as OneDrive, Google Drive, Dropbox, or to a Github repository, and then share a file’s URL.

    https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

    Please show the expected outcome based on the sample data you provided.

    https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523/highlight/true#M607150

    This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.

     

    If your requirement is solved, please make THIS ANSWER a SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.  Proud to be a Super User!

  • 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