Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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% |
Solved! Go to 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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
19 |