Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
LouisB
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 ?

Capture.PNG

Thank you for your help !

1 ACCEPTED SOLUTION

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.

View solution in original post

6 REPLIES 6
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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...

 

Capture.PNG

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thank you Greg,

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

 

aprilaugustcooling_consumptiondecemberelectricity_ecselectricity_heatingelectricity_subscription_pricefebruaryjanuaryjulyjunemarchmaynovemberoctoberprimary_energy_ecsprimary_energy_subscription_priceprofitability_purchase_daysprofitability_purchase_monthsseptemberspecific_electricity_consumptiontotal_annual_heatingtotal_electricitytotal_primary_powerproduct_product_id
49.700.000.00118.990.000.0093.17108.91137.210.000.0091.2511.6171.6650.3073.77291.2919105.74217.49645.38310.661010.4445
12.360.000.0029.59108.54160.5093.1727.0934.120.000.0022.692.8917.8212.510.000.0022421.43193.320.00555.530.0082
55.890.000.00133.820.000.0093.17122.48154.310.000.00102.6213.0680.5956.5781.96291.291496.45241.65725.80334.821099.06104
25.780.000.0050.320.000.00117.7444.8258.070.000.0038.4611.4626.8017.53131.14291.2913260.92386.65274.16504.39696.58109
28.850.000.0069.070.000.00117.7463.2279.650.000.0052.976.7441.6029.20160.590.009183.33483.31374.61601.05535.20110
67.680.000.00162.030.000.00172.04148.31186.850.000.00124.2615.8197.5868.50321.180.00787.81966.61878.821138.661200.00111
181.950.000.00387.080.000.00172.04347.07446.640.000.00302.85130.13211.91150.72295.06291.299346.58869.952204.941041.992791.29118
-53.680.000.00-128.51407.04-697.00172.04-117.62-148.190.000.00-98.55-12.54-77.39-54.330.000.00-26-9-6.20724.960.00607.040.00123
65.86396.61396.61174.130.000.00172.04156.57205.72396.61396.61133.1935.9894.3069.37254.08291.298710.79749.13945.92921.171491.29131
75.44170.59170.59151.80542.72781.89172.04136.70171.22170.59170.59110.168.9884.8641.420.000.00891.30966.610.002463.260.00142
58.920.000.00145.37339.20725.70172.04124.50156.930.000.00101.314.5387.8244.630.000.003091.69604.130.001841.080.00147
51.320.000.00122.870.000.00172.04112.46141.690.000.0094.2311.9974.0051.94221.30291.2926105.92652.46666.43824.511179.01167
27.010.000.0071.400.000.00117.7464.2084.360.000.0054.6214.7538.6728.45163.92291.2921184.43483.31387.88601.05843.10173
63.860.000.00158.710.000.00117.74141.25187.400.000.00122.1441.7685.3468.05109.83291.296816.47323.82884.99441.561286.11184
51.740.000.00100.98352.77550.17172.0489.94116.530.000.0077.1922.9953.7935.170.000.005131.85628.300.001703.280.00190
-70.870.000.00-176.14990.46-982.17172.04-156.76-207.980.000.00-135.55-46.34-94.71-75.520.000.00-11-7-18.281764.070.001944.410.00195
42.870.000.00138.430.000.00172.04130.31173.590.000.00109.669.9980.1568.52196.71291.293089.70579.97763.21752.011251.21200
210.350.000.00522.78298.500.00117.74465.26617.260.000.00402.31137.54281.10224.150.000.0015254.24531.642915.00947.872915.00201

 

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 🙂

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.

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.

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.