Doing SUMIFS in Power BI

[Manny82]

Hello All,

I have been trying to replicate excel Sumifs in Power BI without much luck. I have below excel data and a summarised table. The Table on the right in excel is essentially doing sumifs based on filter selection. For e.g. Sum of Volume for Customer =Cust 1, Product=Hat 1, Plan =A.

The Delta and Delta% is the difference between 2 plans and % variance between them.

In Power BI - I managed to create measures to do all the calculations in a Matrix visual like below, however i am unable to do the Delta and Delta% columns in the Matrix. How can I do this? Any help is greatly appreciated. Thank you.

 

[severynm]

Could you post your sample data in a table I can copy and paste? xl2bb is the best option, but otherwise a regular table is fine too.

 

[Manny82]

PL into PowerBI.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Plan	Customer	Product	Volume	Net Revenue	Net Revenue per unit	COGS	GM	COGS / unit	GM % of NR
2	A	Cust1	Hat 1	100	898	9.0	50	848	0.5	94%			What I am Trying to Achieve in Power BI
3	A	Cust1	Hat 1	6	29	4.8	11	18	1.8	62%			Filters
4	A	Cust1	Hat 2	43	403	9.4	29	374	0.7	93%			Customer	Cust1
5	A	Cust1	Hat 2	79	610	7.7	41	569	0.5	93%			Product	Hat 1
6	A	Cust1	Hat 3	77	432	5.6	37	395	0.5	91%
7	A	Cust1	Hat 3	14	63	4.5	22	41	1.6	65%
8	A	Cust2	Hat 1	12	50	4.2	8	42	0.7	84%
9	A	Cust2	Hat 1	32	51	1.6	33	18	1.0	35%				A	B	Delta	Delta %
10	A	Cust2	Hat 2	59	549	9.3	7	542	0.1	99%			Volume	106	116	10	9%
11	A	Cust2	Hat 2	31	209	6.7	19	190	0.6	91%			Net Revenue	927	192	-735	-79%
12	A	Cust2	Hat 3	44	207	4.7	19	188	0.4	91%			Net Revenue per unit	8.7	1.7	-7.1	-81%
13	A	Cust2	Hat 3	12	24	2.0	5	19	0.4	79%			COGS	61	25	-36	-59%
14	B	Cust1	Hat 1	21	21	1.0	5	16	0.2	76%			GM	866	167	-699	-81%
15	B	Cust1	Hat 1	95	171	1.8	20	151	0.2	88%			Cogs per unit	0.6	0.2	-0.4	-63%
16	B	Cust1	Hat 2	28	182	6.5	46	136	1.6	75%			GM per unit	8.2	1.4	-6.7	-82%
17	B	Cust1	Hat 2	48	369	7.7	12	357	0.3	97%			GM % of NR	93%	87%	-6%	-7%
18	B	Cust1	Hat 3	99	984	9.9	30	954	0.3	97%
19	B	Cust1	Hat 3	66	167	2.5	6	161	0.1	96%
20	B	Cust2	Hat 1	3	27	9.0	3	24	1.0	89%
21	B	Cust2	Hat 1	63	132	2.1	10	122	0.2	92%
22	B	Cust2	Hat 2	78	220	2.8	22	198	0.3	90%
23	B	Cust2	Hat 2	99	501	5.1	4	497	0.0	99%
24	B	Cust2	Hat 3	23	72	3.1	18	54	0.8	75%
25	B	Cust2	Hat 3	33	241	7.3	40	201	1.2	83%
26	C	Cust1	Hat 1	16	21	1.3	5	16	0.3	76%
27	C	Cust1	Hat 1	81	171	2.1	20	151	0.2	88%
28	C	Cust1	Hat 2	1	182	289.0	46	136	73.1	75%
29	C	Cust1	Hat 2	6	369	61.7	12	357	2.0	97%
30	C	Cust1	Hat 3	18	984	54.7	30	954	1.7	97%
31	C	Cust1	Hat 3	54	167	3.1	6	161	0.1	96%
32	C	Cust2	Hat 1	76	27	0.4	3	24	0.0	89%
33	C	Cust2	Hat 1	90	132	1.5	10	122	0.1	92%
34	C	Cust2	Hat 2	46	220	4.8	22	198	0.5	90%
35	C	Cust2	Hat 2	10	501	50.8	4	497	0.4	99%
36	C	Cust2	Hat 3	22	72	3.2	18	54	0.8	75%
37	C	Cust2	Hat 3	71	241	3.4	40	201	0.6	83%
Sheet1

Cell Formulas
Range	Formula
H2:H37	H2	=E2-G2
I2:J37	I2	=G2/D2
N10,O11	N10	=SUMIFS(D:D,$A:$A,N$9,$B:$B,$N$4,$C:$C,$N$5)
O10	O10	=SUMIFS(D:D,$A:$A,O$9,$B:$B,$N$4,$C:$C,$N$5)
P10:P17	P10	=O10-N10
Q10:Q17	Q10	=P10/N10
N11	N11	=SUMIFS(E:E,$A:$A,N$9,$B:$B,$N$4,$C:$C,$N$5)
N12:O12	N12	=N11/N10
N13,O14	N13	=SUMIFS(G:G,$A:$A,N$9,$B:$B,$N$4,$C:$C,$N$5)
O13	O13	=SUMIFS(G:G,$A:$A,O$9,$B:$B,$N$4,$C:$C,$N$5)
N14	N14	=SUMIFS(H:H,$A:$A,N$9,$B:$B,$N$4,$C:$C,$N$5)
N15:O15	N15	=N13/N10
N16:O16	N16	=N14/N10
N17:O17	N17	=N14/N11
F2:F37	F2	=E2/D2

Cells with Data Validation
Cell	Allow	Criteria
N9:O9	List	A,B,C
N4	List	Cust1,Cust2
N5	List	=$S$4:$S$7

 

[Manny82]

@severynm I have uploaded the xl2bb as above. Please advise if this is ok to use? Thanks heaps for looking into this.