Forum Discussion
Helper IISumifs in DAX, using a value in the same row as filter condition
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?
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.
