Forum Discussion
Niels_83
Nov 17, 2025Copper Contributor
power query/ dax measure , multiple conditions/ multiple answers
I have loaded multiple tables in power query table of revenue per category: category revenue EM01 86.000 EM02 68.000 EM03 ...
- Nov 17, 2025
You may use measure like
Shared := VAR TotalRevenue = SUMMARIZE ( Revenue, Revenue[category], "Revenue", SUM ( Revenue[revenue] ) ) VAR AddShares = ADDCOLUMNS ( CROSSJOIN ( TotalRevenue, Shares ), "ToTake", Revenue[category] = Shares[employee] ) VAR Filtered = FILTER ( AddShares, [ToTake] ) VAR KeepColumns = SELECTCOLUMNS ( Filtered, "Employee", [employee], "Manager", [accountmanager], "Revenue", [revenue], "Share, %", [share accountmanager] ) VAR Result = ADDCOLUMNS ( KeepColumns, "Shared", [Revenue] * [Share, %] ) RETURN SUMX ( Result, [Shared] )For Power Query only no changes are required
IlirU
Nov 20, 2025Brass Contributor
How about this?
=LET(pb, PIVOTBY(D2:D12, E2:E12, XLOOKUP(D2:D12, A2:A9, B2:B9) * F2:F12, SUM,, 0,, 1),
VSTACK(pb, IFERROR(1 / (1 / BYCOL(DROP(pb, 1), SUM)), "Total")))
or more shorter is this:
=PIVOTBY(D2:D12, E2:E12, XLOOKUP(D2:D12, A2:A9, B2:B9) * F2:F12, SUM,, 1,, 1)
I think both works to give same results as your formula.
(see my screenshot given in preview post).
BR
IlirU
SergeiBaklan
Nov 21, 2025Diamond Contributor
Category is not unique. For the initial sample try to double it (or check later screenshots)
Your formula gives 86000 as Total for EM01. However, it shall be 2*86000 = 172000.
- IlirUNov 21, 2025Brass Contributor
Ok then, this formula works:
=PIVOTBY(D2:D12, E2:E12, TRANSPOSE(BYCOL(B2:B12 * (TRANSPOSE(D2:D12) = A2:A12) * TRANSPOSE(F2:F12), SUM)), SUM,, 1,, 1)Have a nice day.
IlirU