Showing complete and grouped data in the same chart

[lia_viegas]

Hello,

Another newbie question. I have this kind of data:

ID	Group	Store	Note
1	a	d	10
2	a	d	8
3	a	d	10
4	a	d	7
5	a	d	8
6	a	f	7
7	a	f	10
8	a	f	10
9	a	f	7
10	b	g	10
11	b	g	9
12	b	g	10
13	b	g	9
14	b	g	9
15	b	g	9
16	b	h	10
17	b	h	9
18	b	h	9
19	b	h	8
20	b	h	10
21	c	i	9
22	c	i	9
23	c	i	9
24	c	i	9
25	c	i	10
26	c	j	8
27	c	j	9
28	c	j	8
29	c	j	10
30	c	j	5

I need to create a chart where I can present a bar/line with the mean of all note subjects, a bar/line with the data grouped by Group and one with data grouped by Store. The Store and Group have to be selected by filter and the chart will be something like this:

Maybe the data will be displayed in line by date, but the idea is always to present Total, Group and Store.

Any idea?

Thanks in advance.

Lia

[v-shex-msft]

Hi @lia_viegas,

I not clear how did you create the total bar, in you sample file it doesn't contains the total item(bar/line chart current not support shows the total value) Can you provide something detailed?

>>I need to create a chart where I can present a bar/line with the mean of all note subjects, a bar/line with the data grouped by Group and one with data grouped by Store. The Store and Group have to be selected by filter

For your requirement, you can simply use clustered column chart to achieve your requirement.

Regards,

Xiaoxin Sheng

[lia_viegas]

Hello, @v-shex-msft,

sorry if i'm not clear in explain it to you - English is not my first language.

The total Bar is the data that would appear in the graph if we had no filters at all - in my example, the mean of the note from all subjects. I created that graph in excel, manually, as I have no idea how to do it in Power Bi. From your answer I understand it's impossible, is it right?

I tried clustering, but it does not present the data together as I showed in the image I used as example. Or I see the Group, or I see the store. I need to show them together because my client thinks it's the best way to compare a single store and the group where it is.

Thanks a lot and sorry for my terrible English

[v-shex-msft]

Hi @lia_viegas,

Current bar/column chart not suport total value and multiple labels. Perhaps you can try to manual summary the above table and show the merged value.

Table = 
var temp=SUMMARIZE(Sheet2,Sheet2[Group],"Item",CONCATENATEX(VALUES(Sheet2[Store]),[Store],","),"Total",SUM(Sheet2[Note]))
return
UNION(temp,ROW("Group","Total","Item","Total","Total",SUMX(temp,[Total])))

Regards,

Xiaoxin Sheng