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.
14 Replies
- GilbertQ
Super User
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.
srl01Thank 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".
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 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)]))
