cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Frequent Visitor

## Creating a Graph of Columns Average

Hello everyone,

I am a new user and I am stuck after few hours... And impossible to find a solution on the forum!

I have a table with different Columns: January, February, March etc..

and then each rows correspond to an user. I have the monthly electricity consumption of every user.

I want to display on a graph the average of each column by the month to compare the average of users consumption per month.

I have created first a graph with only the average of each columns on the Y-Axis, but It does'nt look good (photo)...

How can I do to have the months on my X-Axis ?

Thank you for your help !

1 ACCEPTED SOLUTION
Resident Rockstar

Hi @LouisB,

Thanks for your deatils data sample, but is only the month columns useful?

After unpivot your data model, you will get the Month column and another Value column.

Is that the value column the electricity consumption per month?

If you want to calculate the average, you could use calculate(average([value])).

If you still need help, could you share your desired average value so that I can know what value you want to get?

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 REPLIES 6
Super User

You need to unpivot your month columns in your query. Then you can put that column in your x-axis.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

Thank you for your answer, but after transposing my table, how to create a column with the average of all the line ?

So now I have: First column: the months, other columns: each column is an user data...

Super User

Really need sample data that I can copy and paste. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

Thank you Greg,

you right, it is so much cleaner when sharing the datas.

 april august cooling_consumption december electricity_ecs electricity_heating electricity_subscription_price february january july june march may november october primary_energy_ecs primary_energy_subscription_price profitability_purchase_days profitability_purchase_months september specific_electricity_consumption total_annual_heating total_electricity total_primary_power product_product_id 49.70 0.00 0.00 118.99 0.00 0.00 93.17 108.91 137.21 0.00 0.00 91.25 11.61 71.66 50.30 73.77 291.29 19 10 5.74 217.49 645.38 310.66 1010.44 45 12.36 0.00 0.00 29.59 108.54 160.50 93.17 27.09 34.12 0.00 0.00 22.69 2.89 17.82 12.51 0.00 0.00 22 42 1.43 193.32 0.00 555.53 0.00 82 55.89 0.00 0.00 133.82 0.00 0.00 93.17 122.48 154.31 0.00 0.00 102.62 13.06 80.59 56.57 81.96 291.29 14 9 6.45 241.65 725.80 334.82 1099.06 104 25.78 0.00 0.00 50.32 0.00 0.00 117.74 44.82 58.07 0.00 0.00 38.46 11.46 26.80 17.53 131.14 291.29 13 26 0.92 386.65 274.16 504.39 696.58 109 28.85 0.00 0.00 69.07 0.00 0.00 117.74 63.22 79.65 0.00 0.00 52.97 6.74 41.60 29.20 160.59 0.00 9 18 3.33 483.31 374.61 601.05 535.20 110 67.68 0.00 0.00 162.03 0.00 0.00 172.04 148.31 186.85 0.00 0.00 124.26 15.81 97.58 68.50 321.18 0.00 7 8 7.81 966.61 878.82 1138.66 1200.00 111 181.95 0.00 0.00 387.08 0.00 0.00 172.04 347.07 446.64 0.00 0.00 302.85 130.13 211.91 150.72 295.06 291.29 9 3 46.58 869.95 2204.94 1041.99 2791.29 118 -53.68 0.00 0.00 -128.51 407.04 -697.00 172.04 -117.62 -148.19 0.00 0.00 -98.55 -12.54 -77.39 -54.33 0.00 0.00 -26 -9 -6.20 724.96 0.00 607.04 0.00 123 65.86 396.61 396.61 174.13 0.00 0.00 172.04 156.57 205.72 396.61 396.61 133.19 35.98 94.30 69.37 254.08 291.29 8 7 10.79 749.13 945.92 921.17 1491.29 131 75.44 170.59 170.59 151.80 542.72 781.89 172.04 136.70 171.22 170.59 170.59 110.16 8.98 84.86 41.42 0.00 0.00 8 9 1.30 966.61 0.00 2463.26 0.00 142 58.92 0.00 0.00 145.37 339.20 725.70 172.04 124.50 156.93 0.00 0.00 101.31 4.53 87.82 44.63 0.00 0.00 30 9 1.69 604.13 0.00 1841.08 0.00 147 51.32 0.00 0.00 122.87 0.00 0.00 172.04 112.46 141.69 0.00 0.00 94.23 11.99 74.00 51.94 221.30 291.29 26 10 5.92 652.46 666.43 824.51 1179.01 167 27.01 0.00 0.00 71.40 0.00 0.00 117.74 64.20 84.36 0.00 0.00 54.62 14.75 38.67 28.45 163.92 291.29 21 18 4.43 483.31 387.88 601.05 843.10 173 63.86 0.00 0.00 158.71 0.00 0.00 117.74 141.25 187.40 0.00 0.00 122.14 41.76 85.34 68.05 109.83 291.29 6 8 16.47 323.82 884.99 441.56 1286.11 184 51.74 0.00 0.00 100.98 352.77 550.17 172.04 89.94 116.53 0.00 0.00 77.19 22.99 53.79 35.17 0.00 0.00 5 13 1.85 628.30 0.00 1703.28 0.00 190 -70.87 0.00 0.00 -176.14 990.46 -982.17 172.04 -156.76 -207.98 0.00 0.00 -135.55 -46.34 -94.71 -75.52 0.00 0.00 -11 -7 -18.28 1764.07 0.00 1944.41 0.00 195 42.87 0.00 0.00 138.43 0.00 0.00 172.04 130.31 173.59 0.00 0.00 109.66 9.99 80.15 68.52 196.71 291.29 30 8 9.70 579.97 763.21 752.01 1251.21 200 210.35 0.00 0.00 522.78 298.50 0.00 117.74 465.26 617.26 0.00 0.00 402.31 137.54 281.10 224.15 0.00 0.00 15 2 54.24 531.64 2915.00 947.87 2915.00 201

What we need is a chart with the average electricity consumption per month (Only the Month's columns are usefull).

So in excel I would calculate the averages of each columns named: January, February, March, etc... and draw the results on Y with the months on X.

Thanks again for your precious help 🙂

Resident Rockstar

Hi @LouisB,

Thanks for your deatils data sample, but is only the month columns useful?

After unpivot your data model, you will get the Month column and another Value column.

Is that the value column the electricity consumption per month?

If you want to calculate the average, you could use calculate(average([value])).

If you still need help, could you share your desired average value so that I can know what value you want to get?

Best Regards,

Cherry

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

Hi @v-piga-msft,

first thank you so much for your help.

You got it ! The solution was to Unpivot and not just Pivot my table 🙂

Then Group the rows by Month and setting the average of all the values.

Thank you all so much for your help.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors