Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
srl01
Helper II
Helper II

Sumifs 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

ItemBrandScore
ABCBrandA1
DEFBrandB10
GHIBrandC100
JKLBrandD1000

 

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])
BasketIDItemBrandItemBrandScoreBasketBrandScore
1ABCBrandA1111
1DEFBrandB10111
1GHIBrandC100111
2ABCBrandA111
2DEFBrandB1011
3GHIBrandC100100
4DEFBrandB101110
4GHIBrandC1001110
4JKLBrandD10001110

 

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?

 

1 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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

View solution in original post

14 REPLIES 14
miuranium
Frequent Visitor

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

 

itemstockgroup (related from other table)subtotal by group using sumifs
15a15
25a15
35a15
45b10
55b10
65c5

Hi there this measure below will work

 

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

image.png





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

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

miuranium
Frequent Visitor

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

 

itemstockgroup (related from other table)subtotal by group using sumifs
15a15
25a15
35a15
45b10
55b10
65c5
GilbertQ
Super User
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.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

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".

 

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?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

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

 

 

    
BasketIDItemBasketBrand 
1ABCMix<-- Because the basket contains items from both BrandA and BrandC
1DEFMix<-- Because the basket contains items from both BrandA and BrandC
1GHIMix<-- Because the basket contains items from both BrandA and BrandC
2ABCBrand A<--Basket contains two items but both are from Brand A
2DEFBrand A<--Basket contains two items but both are from Brand A
3GHIBrand C<--Basket contains only one item, from Brand C
4DEFMix<-- Because the basket contains items from BrandA, BrandC and Brand D
4GHIMix<-- Because the basket contains items from BrandA, BrandC and Brand D
4JKLMix<-- 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.

 

ItemBrand
ABCBrandA
DEFBrandA
GHIBrandC
JKLBrandD

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?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

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.

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

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

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

 

Shane

Hi @srl01

 

That is awesome well done for getting it working.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.