Forum Discussion
How to do Excel Sumif in PowerBI?
Hello,
Suppose I have the following (made up) data below:
| 1 | B | C | D | E | F | G | H | I |
| 2 | Stock | Amount Invested | Low Projected Value | Low Rank | Mid Projected Value | Mid Rank | High Projected Value | High Rank |
| 3 | Amazon | $34,376.00 | $103,128.00 | Better | $154,692.00 | Good | $206,256.00 | Better |
| 4 | Tesla | $85,544.00 | $256,632.00 | Best | $384,948.00 | Best | $513,264.00 | Best |
| 5 | Microsoft | $45,333.00 | $226,665.00 | Best | $339,997.50 | Best | $453,330.00 | Best |
| 6 | $40,473.00 | $121,419.00 | Better | $182,128.50 | Good | $242,838.00 | Better | |
| 7 | Amazon | $91,208.00 | $91,208.00 | Better | $136,812.00 | Better | $182,416.00 | Better |
| 8 | Netflix | $9,027.00 | $9,027.00 | Bad | $13,540.50 | Bad | $18,054.00 | Bad |
| 9 | Spotify | $35,476.00 | $70,952.00 | Good | $106,428.00 | Better | $141,904.00 | Good |
| 10 | Uber | $24,444.00 | $24,444.00 | Bad | $36,666.00 | Bad | $48,888.00 | Bad |
My goal is to create this table, where it aggregates the low, mid, and high values based on the rank.
| Low Projected Value | Mid Projected Value | High Projected Value | |
| Bad | $33,471.00 | $50,206.50 | $66,942.00 |
| Good | $70,952.00 | $336,820.50 | $141,904.00 |
| Better | $315,755.00 | $243,240.00 | $631,510.00 |
| Best | $483,297.00 | $724,945.50 | $966,594.00 |
in Excel, it's easy. I would just do this:
| B | C | D | E | |
| Low Projected Value | Mid Projected Value | High Projected Value | ||
| 15 | Bad | =SUMIF($E$3:$E$10,B15,$D$3:$D$10) | =SUMIF($G$3:$G$10,B15,$F$3:$F$10) | =SUMIF($I$3:$I$10,B15,$H$3:$H$10) |
| 16 | Good | =SUMIF($E$3:$E$10,B16,$D$3:$D$10) | =SUMIF($G$3:$G$10,B16,$F$3:$F$10) | =SUMIF($I$3:$I$10,B16,$H$3:$H$10) |
| 17 | Better | =SUMIF($E$3:$E$10,B17,$D$3:$D$10) | =SUMIF($G$3:$G$10,B17,$F$3:$F$10) | =SUMIF($I$3:$I$10,B17,$H$3:$H$10) |
| 18 | Best | =SUMIF($E$3:$E$10,B18,$D$3:$D$10) | =SUMIF($G$3:$G$10,B18,$F$3:$F$10) | =SUMIF($I$3:$I$10,B18,$H$3:$H$10) |
So the sumif for the low, mid, and high values are going off of the low, mid, and high ranks, respectively. The output table is not just off of one column, but will need to be three columns. However, I am struggling to get all these calculations in one table in PowerBI. I've tried putting this in the matrix visual, but this only lets me pivot off of one column. I've tried writing some measures in DAX, but I couldn't get that either.
Thanks!
Edit:
Clarity in the output table
Anonymous here is a solution with separate queries (easy to chew), check Power Queries, and the new table is called Goodbad Separate
Cheers!!
8 Replies
- parry2k
Super User
Anonymous I haven't used SUMIF but what is B15,B16,B17,B18 values which are used in SUMIF formula you provided.
Hi parry2k , yes, I apologize, it is unclear. The output table I made for B15 to B18 is supposed to refer to the values Bad to Best at the left of the table. The sumif formula is referencing those words as the criteria in the criteria range of the sumif formula.
B C D E Low Projected Value Mid Projected Value High Projected Value 15 Bad =SUMIF($E$3:$E$10,B15,$D$3:$D$10) =SUMIF($G$3:$G$10,B15,$F$3:$F$10) =SUMIF($I$3:$I$10,B15,$H$3:$H$10) 16 Good =SUMIF($E$3:$E$10,B16,$D$3:$D$10) =SUMIF($G$3:$G$10,B16,$F$3:$F$10) =SUMIF($I$3:$I$10,B16,$H$3:$H$10) 17 Better =SUMIF($E$3:$E$10,B17,$D$3:$D$10) =SUMIF($G$3:$G$10,B17,$F$3:$F$10) =SUMIF($I$3:$I$10,B17,$H$3:$H$10) 18 Best =SUMIF($E$3:$E$10,B18,$D$3:$D$10) =SUMIF($G$3:$G$10,B18,$F$3:$F$10) =SUMIF($I$3:$I$10,B18,$H$3:$H$10) - parry2k
Anonymous see attached, look at table GoodBad and Rank, and all the efforts are in data prep, following the best practice with scalable solution.
Ignore other tables in the file, those for not in use.
I would 💖 Kudos 🙂 if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
- MFelixHi Anonymous,
In this case the best option is to unpivot all columns for project amount and rank
Then create a matrix visualization with
rank on rows
Project type on columns
Project value on values.
Should give you expected amounts - Ashish_Mathur
