How to do Excel Sumif in PowerBI?

[Anonymous]

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	Google	$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

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[MFelix]

Hi @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

[parry2k]

@Anonymous I haven't used SUMIF but what is B15,B16,B17,B18 values which are used in SUMIF formula you provided.

[Anonymous]

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!

[Anonymous]

Thank you very much @parry2k  and @MFelix 

Parry, I downloaded your file and looked at the Power Query, and it is very clean. It definitely answers my question, and I will study it and try to implement it. 

One thing is that my actual file has like 50 columns of data, and in my final output table, I will have to add additional columns that may not be compatible with the new transformed format.

Do you think there is a DAX formula that is similar to how the sumif formula works in Excel? The DAX formula would reference the Rows value  in the table visual (bad, good, better, best) to be the criteria parameter, then I could select any field/column in my data table as the criteria range parameter, and then select any field/column in my data table as the sum range parameter. 

I know you can do something like:

CALCULATE( SUM(Table[Sum field]), Table[Criteria field]) = "Best")         which is sort of similar.  Could I adjust this formula to dynmically reference the current row name in the matrix/table visual? 

Thanks again!

[parry2k]

@MFelix direct unpivot will not work because rank is different for each high/low/mid-value, with unpivot, it will require different measures for each type of value but appending the tables (a solution I provided), everything is straight forward from there.

I just appended the table in one power query but it will be easier to do it as different queries, easy to manage.

here is the output

[parry2k · Accepted solution]

@Anonymous here is a solution with separate queries (easy to chew), check Power Queries, and the new table is called Goodbad Separate

Cheers!!