SUMIFS in Power BI

Question

I need to create 2 SUMIFS columns in Power Bi with multiple criteria. One column will be "Per Charge Per month" to find the total charges with "Sum of Charge Amount", "charge bucket", "Type", and "Delivery Month Column" columns as criteria. The other column will have the same criteria but instead of "Sum Of charge Amount" column it will be the "Number Of Containers" column.

I used to do this in excel by using the SUMIFS formula.

This is the SUMIFS Formula in the "Per Charge Per Month" Column


=SUMIFS($D$2:$D$11,$G$2:$G$11,G2,$B$2:$B$11,B2,$C$2:$C$11,C2)

This is the SUMIFS Formula in the "Container Per Month" Column


=SUMIFS($E$2:$E$11,$G$2:$G$11,G2,$B$2:$B$11,B2,$C$2:$C$11,C2)

I have tried to make these columns in Power BI but I can't even create a SUMIF calculated column with just 1 criteria. I used to formula below to try create a column that shows the total number of containers in each month. But it just shows the number of containers in that row. I don't even know how I would add more criteria. Any advice would be really appreciated!

SUMIF Container Per Month = 
VAR vRowShippingMonth = 'Invoice Charges'[Shipping month]
Return
CALCULATE(
    SUM('Invoice Charges'[Number of Containers]),
    'Invoice Charges'[Shipping month] = vRowShippingMonth)

Row Labels	Charge Bucket	Type	Sum of Charge Amount	Number of containers	Per Container	Delivery Month	Per Charge Per Month	Container Per Month	Weighted Average Cost
ID1	Ancillary- Non Discretionary	40	5,805.00	9	645.00	4/1/2021	5,805.00	9	645.00
ID2	Base Charge	40	12290	3	4,096.67	4/1/2021	12,290.00	3	4,096.67
ID3	Origin Charge	40	1,957.00	3	652.33	4/1/2021	1,957.00	3	652.33
ID4	Ancillary- Non Discretionary	40	867.68	4	216.92	5/1/2021	867.68	4	216.92
ID5	Base Charge	40	10805	2	5,402.50	5/1/2021	10,805.00	2	5,402.50
ID6	Origin Charge	40	1351	2	675.50	5/1/2021	1,351.00	2	675.50
ID7	Ancillary- Non Discretionary	40	1,790.00	2	895.00	6/1/2021	1,790.00	2	895.00
ID8	Base Charge	40	20101	2	10,050.50	6/1/2021	40,301.00	4	10,075.25
ID9	Origin Charge	40	1,421.50	2	710.75	6/1/2021	1,421.50	2	710.75
ID10	Base Charge	40	20200	2	10,100.00	6/1/2021	40,301.00	4	10,075.25

How about abstracting the problem like shown at stackoverflow.com/help/minimal-reproducible-example, or would that be a disproportionate effort? After all you are asking for voluntary help ... — Peter, Aug 05 '22 at 21:00
Hello @Spen820 please show us some efforts by sharing your code in power bi. Otherwise, It would be useless to bring the screenshots of your excel sheets into this platform. please work on this topic a little more, then if you can't solve, then we can make some advice here. Your effort is paramount here. Stay safe! — Ozan Sen, Aug 05 '22 at 21:06
Upload data and code as **text** please. Not as images. [Why should I not upload images of code/data/errors when asking a question?](https://meta.stackoverflow.com/questions/285551/why-should-i-not-upload-images-of-code-data-errors-when-asking-a-question) — Ron Rosenfeld, Aug 05 '22 at 21:25
Hello! Sorry about that I'm still knew to Power BI and Stackflow. Let me know If I added enough information or if there's anything I left out. — Spen820, Aug 07 '22 at 20:20

Ron Rosenfeld · Accepted Answer · 2022-08-08T01:02:00.043

Try:

Per Charge per Month = 
    CALCULATE(
        SUM([Sum of Charge Amount]), 
        FILTER('Table', 
            EARLIER([Delivery Month]) = [Delivery Month] &&
            EARLIER([Charge Bucket]) = [Charge Bucket]))

Container per month = 
    CALCULATE(
        SUM([Number of containers]), 
        FILTER('Table', 
            EARLIER([Delivery Month]) = [Delivery Month] &&
            EARLIER([Charge Bucket]) = [Charge Bucket]))

Produces the same results you show in your table.

Note that the filter for the month works because all of the dates are on the same day of the month (1). If that was not the case, you'd have to add a column that shows year and month, and filter on that.

SUMIFS in Power BI

1 Answers1