Sumifs in DAX, using a value in the same row as filter condition

[srl01]

I am looking to do an analysis on retail data structured with one line per item sold, and unique BasketIDs for each basket of items.

Would like to "score" each basket by the combination of brands in the basket (i.e. all item purchases found elsewhere in the table with the same BasketID), and apply that score to each item.

First I have an item / brand / score master Table2

Item	Brand	Score
ABC	BrandA	1
DEF	BrandB	10
GHI	BrandC	100
JKL	BrandD	1000

And then the calcs I want, with Excel functions.

(Note the Brand column below is superfluous, but included for clarity)

(raw data)	(raw data)	=VLOOKUP([@Item],Table2,2,FALSE)	=VLOOKUP([@Item],Table2,3,FALSE)	=SUMIFS([ItemBrandScore],[BasketID],[@BasketID])
BasketID	Item	Brand	ItemBrandScore	BasketBrandScore
1	ABC	BrandA	1	111
1	DEF	BrandB	10	111
1	GHI	BrandC	100	111
2	ABC	BrandA	1	11
2	DEF	BrandB	10	11
3	GHI	BrandC	100	100
4	DEF	BrandB	10	1110
4	GHI	BrandC	100	1110
4	JKL	BrandD	1000	1110

So for example, basket 1 contains three items, one each from BrandA, BrandB and BrandC.

These have Brand scores of 1,10,100 respectively. So the Score for basket 1 is 1+10+100 = 111.

How can I replicate this functionality - specifically the SUMIFS part -using DAX, assuming with tables structured the same way?

[Anonymous]

@GilbertQ

hi, thanks for your effort, I had the same issue but in a different way. 

i have one table showing item with stock. another table showing the group of each item. added the group column into the first table using relate function. now i want to create new column with same functionality of sumifs in excel. example last column below. 

item	stock	group (related from other table)	subtotal by group using sumifs
1	5	a	15
2	5	a	15
3	5	a	15
4	5	b	10
5	5	b	10
6	5	c	5

[GilbertQ]

Hi there this measure below will work

SubTotal = CALCULATE(SUM('Table1'[stock]),ALLEXCEPT(Table1,'Table1'[group (related from other table)]))

[Anonymous]

thanks Gilbert, how can i make it as column not measre. as i will have to use that column into other furmulas. 

[Anonymous]

@GilbertQ

hi, thanks for your effort, I had the same issue but in a different way. 

i have one table showing item with stock. another table showing the group of each item. added the group column into the first table using relate function. now i want to create new column with same functionality of sumifs in excel. example last column below. 

item	stock	group (related from other table)	subtotal by group using sumifs
1	5	a	15
2	5	a	15
3	5	a	15
4	5	b	10
5	5	b	10
6	5	c	5

[GilbertQ]

Hi there, it would depend on how you want to visualize this data.

What you could do is to have a Brand table which will have the Brands and their Score. Which you could then link to your main table using the Brand columns from your main table and your Brand table.

After which you could then create a measure which would be the Brand Score = sum('Brand'[Score])

And then in a table put in the BasketID and then your measure "Brand Score" this will then show the total.

[srl01]

Thank you for your response.

However from your suggestion it is not clear to me what I would actually do in DAX to achieve the outcome required. 

I already have a main table and a BrandTable setup as you described, and the 1:n relationship is established (using Brand coulmn).

Basically the key challenge here is replicating the functionality of the SUMIFS that filters across all rows to find those with the same BasketID as the current row. 

Ultimately what I want to do is to be able to associate each Basket with the brand of items contained - if a Basket is comprised only of items in BrandA, then the BasketBrand would be BrandA, and so on. And of course some Baskets will be a mix of different brands; those would be associated with BasketBrand value "Mix".

[GilbertQ]

Hi @srl01

Ok so just so that I understand you would like 111 to be represented across all the rows for each of the BasketID's?

[srl01]

@GilbertQno that is not my intention. BasketBrandScore changes depending on the content of the basket.

Let me frame up another way then.

If I can get the column BasketBrand setup as below, I am set.

BasketID	Item	BasketBrand
1	ABC	Mix	<-- Because the basket contains items from both BrandA and BrandC
1	DEF	Mix	<-- Because the basket contains items from both BrandA and BrandC
1	GHI	Mix	<-- Because the basket contains items from both BrandA and BrandC
2	ABC	Brand A	<--Basket contains two items but both are from Brand A
2	DEF	Brand A	<--Basket contains two items but both are from Brand A
3	GHI	Brand C	<--Basket contains only one item, from Brand C
4	DEF	Mix	<-- Because the basket contains items from BrandA, BrandC and Brand D
4	GHI	Mix	<-- Because the basket contains items from BrandA, BrandC and Brand D
4	JKL	Mix	<-- Because the basket contains items from BrandA, BrandC and Brand D

And I have raw data for the first two columns above, and a lookup table as below.

Item	Brand
ABC	BrandA
DEF	BrandA
GHI	BrandC
JKL	BrandD

[GilbertQ]

Hi @srl01

What I would do is to still create a measure which would add up the values based on the Brand Score.

I would then have another mapping table which would have all the related Brand Scores

EG: 1, 10, 100, 111 etc.

And associated to each of these records I would then have what represents those numbers. (Which is what you have put in your example)

Then finally all that you would need to do, is to create a lookup or another measure where it finds the Brand Score and then show the associated text?

[srl01]

Hello @GilbertQ

---

What I would do is to still create a measure which would add up the values based on the Brand Score.

Ok, so I have BrandScore:=sum(BrandScore[Score]) setup in my main table.

I would then have another mapping table which would have all the related Brand Scores

EG: 1, 10, 100, 111 etc.

And associated to each of these records I would then have what represents those numbers. (Which is what you have put in your example) 

---

Yep, all set on that too - numbers mapped to various text values for BasketBrand.

---

Then finally all that you would need to do, is to create a lookup or another measure where it finds the Brand Score and then show the associated text?

---

This part I don't get. Firstly, I need to be able to filter on BasketBrand (e.g. using slicers) so needs to be a coulmn not a measure, right? But more fundamentally, I don't see how this approach isolates only the items in the same basket (i.e. those with the same basketID). Put another way I don't see anything equivalent to the SUMIFS in the excel example I originally pasted, which adds up brand scores only for the rows with the same basketID as the current row.

[v-huizhn-msft]

Hi @srl01,

---

---

Put another way I don't see anything equivalent to the SUMIFS in the excel example I originally pasted, which adds up brand scores only for the rows with the same basketID as the current row.

You can create a calculated column using the formula.

BasketBrandScore=CALCULATE(SUM(Table[ItemBrandScore]),ALLEXCEPT(Table,Table[BasketID]))

Then you can create a calculated column using the [BasketBrandScore] column, so that you can get [BasketBrand], please see the formula below.

BasketBrand=IF(Table[BasketBrandScore] in {11,110,101,111},"Mix",Table[Brand])

Please don't hesitate to ask if you have any other problems.

Best Regards,
Angelia

[GilbertQ · Accepted solution]

Hi @srl01

Ok apologies for sometimes going around in circles because I am trying to understand your requirements.

What I would then do is go into the Query Editor and copy the existing table (Source Table) where you have the details. Then in this second table (Brand Group) I would then look to get the Band Scoring per Basket ID. This could possibly be done with the Group By.

After which I would then merge my first table (Source Table) with my second table (Brand Group) on the Basket ID using a left join.

What this would then do is to take the Band Total and put it across all the rows in your first table (Source Table).

And then from there you could easily create a conditional column based on the Band Score.

That would then allow you to have all your data in columns in your dataset.

[srl01]

Huge thanks @GilbertQ. Took me a while but I have this working now using separate queries and groupby as you suggested.

Shane

[GilbertQ]

Hi @srl01

That is awesome well done for getting it working.