Creating a Graph of Columns Average

[LouisB]

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 !

[Greg_Deckler]

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

[LouisB]

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

[Greg_Deckler]

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

[LouisB]

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 🙂

[v-piga-msft · 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

[LouisB]

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.