Forum Discussion
How to do a calculation formula with multiple filters
Hi! I am learning Power Bi and I usually use a SUMIFS statement: I have 2 data tables this is what I want it to read: Bring back units booked if season = season column,, na planning org = na planning org column, the gender = NA gender column and the division = the division column I hope that makes sense:
I am pulling the units booked from a different table,
Hi! Thank you for the response. My origional questions was confusing... What I needed to do is build more relationships. After I did that I was able to do a simple formula to bring in another data source.
Thank you!
4 Replies
This is how I would write the formula:
=IFERROR(SUMIFS('[BY18 MASTER FILE.xlsx]BOOKINGS_DATA'!$AT:$AT,'[BY18 MASTER FILE.xlsx]BOOKINGS_DATA'!$B:$B,$B2,'[BY18 MASTER FILE.xlsx]BOOKINGS_DATA'!$G:$G,$C2,'[BY18 MASTER FILE.xlsx]BOOKINGS_DATA'!$K:$K,$D2,'[BY18 MASTER FILE.xlsx]BOOKINGS_DATA'!$J:$J,$E2),0)
I'm having trouble working out what you are exactly trying to? Do you mean you'd like to use multiple columns to combine together to form your primary key? I.e. you return 'Units Booked' based on the combination of 'Season', 'NA Planning Org', 'NA Gender' and 'Div Desc'?
For both Excel and Power BI i'd recommand creating a column called "LookupID" which is a formula along the lines of
=[Column1] & [Column2] &...&[ColumnN]
Where ColumnX is each of your columns which will form to create a unique value.
Then you can instruct your Vlookups in excel to use these columns. In Power BI you can link your tables using this lookup table.Hi! Thank you for the response. My origional questions was confusing... What I needed to do is build more relationships. After I did that I was able to do a simple formula to bring in another data source.
Thank you!
Eric_ZhangMicrosoft Employee
STYLER44 wrote:
This is how I would write the formula:
=IFERROR(SUMIFS('[BY18 MASTER FILE.xlsx]BOOKINGS_DATA'!$AT:$AT,'[BY18 MASTER FILE.xlsx]BOOKINGS_DATA'!$B:$B,$B2,'[BY18 MASTER FILE.xlsx]BOOKINGS_DATA'!$G:$G,$C2,'[BY18 MASTER FILE.xlsx]BOOKINGS_DATA'!$K:$K,$D2,'[BY18 MASTER FILE.xlsx]BOOKINGS_DATA'!$J:$J,$E2),0)
There's no SUMIF in Power BI, as a workaround you can try
total = SUMX ( FILTER ( YourTable, 1 = 1 && 2 = 2 && moreconditions), valueFiled )
