Using O365
In F2, find sum for Type "Revenue" and Group "A" using the Amount & Allocation % Tables.
F2 = $10,000 * 10 % + $15,000 * 20% = $4,000
10% = ACCT + A
20% = BRRT + A
I’m trying to avoid adding helper columns to the Amount Table such as:
Using =XLOOKUP(Table1[@[Dept]:[Dept]]&Table1[[#Headers],[A]],Table2[[Dept]:[Dept]]&Table2[[Group]:[Group]],Table2[[Alloc%]:[Alloc%]],0)*Table1[@[Amount]:[Amount]]
So far, I am stumped about how to proceed without the use of Helper Columns and a SUMIFS in J2. This method would use too many resources given my dataset size (200k rows).
Any ideas? SUMPRODUCT with SUMIFS? Power BI table links and DAX? TIA
>Solution :
you are on the right tract with SUMPRODUCT and SUMIFS:
=SUMPRODUCT(SUMIFS(Table1[[Amount]:[Amount]],Table1[[Type]:[Type]],Table4[@[Type]:[Type]],Table1[[Dept]:[Dept]],Table2[[Dept]:[Dept]])*Table2[[Alloc %]:[Alloc %]]*(Table2[[Group]:[Group]]=Table4[[#Headers],[A]]))
- Table1 = A1:C5
- Table2 = A7:15
- Table4 = E1:I3
