SUMIFS in excel to power bi with multiple criteria

[Anonymous]

Hi I am having trouble implementing a column with this excel formula:

=SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2_criteria2)  into power bi. 

I tried implementing in excel and load it to power bi using one excel file, it worked but I have multiple excel files using this formula, and when I use the get data from folders, combining the multiple excel files as one and load it, there are blanks for this column with the formula. That's why I want to implement this forumla in Power BI.

I know that CALCULATE together with SUM is the SUMIFS in Power BI but the filter part [ column =" "] is only specific to it.

The following column is a example of the kind of data I'm doing.

I need to implement column E into power BI

in excel i used  SUMIFS($D:$D,$A:$A,A2,$C:$C,C2), so my criteria is based on each row's location and date. Any idea on how to replicate column E in Power BI as a calculated column in data model? Column A,B,C and D are already generated in the data model. To better help in understanding, the visual in power BI I'm using is a pie chart with Column B as the Legend, Column C as the Date and Column F as the values. I have 2 slicers controlling the date and location. I know that piechart already has a details section where you can change the pie chart to show the percent of the day for you already, but I still need that Column F for other uses. Would be greatful if anyone can help me solve this!

Table example:

	A	B	C	D	E	F
1	Location	Menu Item	Date	Orders	Total orders of the day	Percentage of the day
2	East	fries	01/02/2001	20	55	36.36%
3	West	fries	01/02/2001	35	48	72.92%
4	South	fries	01/02/2001	17	29	58.62%
5	East	burger	01/02/2001	35	55	63.64%
6	West	burger	01/02/2001	13	48	27.08%
7	South	burger	01/02/2001	12	29	41.38%

[dedelman_clng · Accepted solution]

Hi @Anonymous - is this what you're looking for?

All Orders (per location and date)

All Orders =
CALCULATE (
    SUM ( Orders[Orders] ),
    ALLEXCEPT ( Orders, Orders[Location], Orders[Date] )
)

Percentage of the Day

Pct of the Day = DIVIDE(SUM(Orders[Orders]), [All Orders], 0)

Hope this helps

David

[ShivaniDhoble]

Hi,

I need the sumif formula from a text column. there are 2 columns one is quantity and another column denoting 2 break up (ok production & scrap). I need a formula which will calculate the total of Ok production quantity and divide with total Quantity.

can anyone help me with the formula.

Regards,

Shivani