Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
Solved! Go to Solution.
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!
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)
@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 )
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
107 | |
105 | |
87 | |
61 |