Forum Discussion
power query/ dax measure , multiple conditions/ multiple answers
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
And what if there are multiple rows per EM in revenue table? Then it's not possible to make a connections between revenue table and AM table.
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
Your measure is really working fine! Thanks SergeiBaklan​!
If in revenue table also column with years is added: do also have measure to get variance in revenue in year X (selected with slicer) compared to year X-1?
Updated measure a bit. Idea is the same, but with GENERATE
Shared by := VAR TotalRevenue = SUMMARIZE ( Revenue, Revenue[category], "Revenue", SUM ( Revenue[revenue] ) ) VAR Result = GENERATE ( TotalRevenue, SELECTCOLUMNS ( FILTER ( Shares, Revenue[category] = Shares[employee] ), "Shared", [Revenue] * [share accountmanager] ) ) RETURN SUMX ( Result, [Shared] )Niels_83​ , do you work only with years, or consider periods as months, quarters, YTD, etc.?
And it's not clear how to show result, perhaps you may expand your Answer sample with periods variances.
Also, perhaps "share accountmanagers" differ from period to period.
I have separate column for year (number) and month (text). It would be great if you have measure for variance in revenue in selected year (selected with slicer) compared to year-1. And maybe even year to month (cumulative). Something like (from pivot tabel):