Doing SUMIFS in Power BI

Manny82

New Member
Joined
Jan 3, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
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.

1637195430386.png


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.

1637195766028.png
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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.
 
Upvote 0
PL into PowerBI.xlsx
ABCDEFGHIJKLMNOPQ
1PlanCustomerProductVolumeNet RevenueNet Revenue per unitCOGSGMCOGS / unitGM % of NR
2ACust1Hat 11008989.0508480.594%What I am Trying to Achieve in Power BI
3ACust1Hat 16294.811181.862%Filters
4ACust1Hat 2434039.4293740.793%CustomerCust1
5ACust1Hat 2796107.7415690.593%ProductHat 1
6ACust1Hat 3774325.6373950.591%
7ACust1Hat 314634.522411.665%
8ACust2Hat 112504.28420.784%
9ACust2Hat 132511.633181.035%ABDeltaDelta %
10ACust2Hat 2595499.375420.199%Volume106116109%
11ACust2Hat 2312096.7191900.691%Net Revenue927192-735-79%
12ACust2Hat 3442074.7191880.491%Net Revenue per unit8.71.7-7.1-81%
13ACust2Hat 312242.05190.479%COGS6125-36-59%
14BCust1Hat 121211.05160.276%GM866167-699-81%
15BCust1Hat 1951711.8201510.288%Cogs per unit0.60.2-0.4-63%
16BCust1Hat 2281826.5461361.675%GM per unit8.21.4-6.7-82%
17BCust1Hat 2483697.7123570.397%GM % of NR93%87%-6%-7%
18BCust1Hat 3999849.9309540.397%
19BCust1Hat 3661672.561610.196%
20BCust2Hat 13279.03241.089%
21BCust2Hat 1631322.1101220.292%
22BCust2Hat 2782202.8221980.390%
23BCust2Hat 2995015.144970.099%
24BCust2Hat 323723.118540.875%
25BCust2Hat 3332417.3402011.283%
26CCust1Hat 116211.35160.376%
27CCust1Hat 1811712.1201510.288%
28CCust1Hat 21182289.04613673.175%
29CCust1Hat 2636961.7123572.097%
30CCust1Hat 31898454.7309541.797%
31CCust1Hat 3541673.161610.196%
32CCust2Hat 176270.43240.089%
33CCust2Hat 1901321.5101220.192%
34CCust2Hat 2462204.8221980.590%
35CCust2Hat 21050150.844970.499%
36CCust2Hat 322723.218540.875%
37CCust2Hat 3712413.4402010.683%
Sheet1
Cell Formulas
RangeFormula
H2:H37H2=E2-G2
I2:J37I2=G2/D2
N10,O11N10=SUMIFS(D:D,$A:$A,N$9,$B:$B,$N$4,$C:$C,$N$5)
O10O10=SUMIFS(D:D,$A:$A,O$9,$B:$B,$N$4,$C:$C,$N$5)
P10:P17P10=O10-N10
Q10:Q17Q10=P10/N10
N11N11=SUMIFS(E:E,$A:$A,N$9,$B:$B,$N$4,$C:$C,$N$5)
N12:O12N12=N11/N10
N13,O14N13=SUMIFS(G:G,$A:$A,N$9,$B:$B,$N$4,$C:$C,$N$5)
O13O13=SUMIFS(G:G,$A:$A,O$9,$B:$B,$N$4,$C:$C,$N$5)
N14N14=SUMIFS(H:H,$A:$A,N$9,$B:$B,$N$4,$C:$C,$N$5)
N15:O15N15=N13/N10
N16:O16N16=N14/N10
N17:O17N17=N14/N11
F2:F37F2=E2/D2
Cells with Data Validation
CellAllowCriteria
N9:O9ListA,B,C
N4ListCust1,Cust2
N5List=$S$4:$S$7
 
Upvote 0
@severynm I have uploaded the xl2bb as above. Please advise if this is ok to use? Thanks heaps for looking into this.
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,109
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top