power query/ dax measure , multiple conditions/ multiple answers

Question

I have loaded multiple tables in power querytable of revenue per category:categoryrevenueEM01&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 86.000EM02&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 68.000EM03&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 93.000EM04&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 87.000EM05&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6.000EM06&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 96.000EM07&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 87.000EM08&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 54.000&nbsp;table of share per category per accountmanager:employeeaccountmanagershare accountmanagerEM01AM175%EM01AM225%EM02AM1100%EM03AM2100%EM04AM3100%EM05AM260%EM05AM340%EM06AM1100%EM07AM150%EM07AM350%EM08AM2100%&nbsp;Note: in same cases (EM01, EM05, EM07) multiple accountmanagers are in charge for a category. Total of share per category is always 100%.How to organize in power query to connect both tables to get table of revenue per per category per accountmanager? Use measure? Which? Or other solution?&nbsp;Answer (in pivot table) should be:employeeAM1AM2AM3totalEM01&nbsp;&nbsp;&nbsp;&nbsp; 64.500&nbsp;&nbsp;&nbsp;&nbsp; 21.500&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 86.000EM02&nbsp;&nbsp;&nbsp;&nbsp; 68.000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 68.000EM03&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 93.000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 93.000EM04&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 87.000&nbsp;&nbsp;&nbsp;&nbsp; 87.000EM05&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3.600&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2.400&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6.000EM06&nbsp;&nbsp;&nbsp;&nbsp; 96.000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 96.000EM07&nbsp;&nbsp;&nbsp;&nbsp; 43.500&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 43.500&nbsp;&nbsp;&nbsp;&nbsp; 87.000EM08&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 54.000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 54.000&nbsp;

harun24hr · Answer

Here is a formula approach if table are loaded to sheet.=PIVOTBY(D2:D12,E2:E12,XLOOKUP(D2:D12,A2:A9,B2:B9)*F2:F12,SUM,0,0)&nbsp;

sergeibaklan · Answer

As variant you may add both tables to data model with relationship asAdd measureShared:=SUMX (
    Shares,
    Shares[share accountmanager]
        * RELATED ( Revenue[revenue] )
)and build PivotTable from data model using above measure

sergeibaklan · Answer

You may use measure likeShared :=
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

oliverscheurich · Answer

An alternative could be Power Query. In the attached file you can add data to the blue dynamic tables. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.

sergeibaklan · Answer

Power Query variant with Grand Totallet
    Source = Table.Join(Shares, {"employee"}, Revenue, {"category"}, JoinKind.LeftOuter),
    AddShared = Table.AddColumn(Source, "Shared", each [share accountmanager]*[revenue], Currency.Type),
    SelectColumns = Table.SelectColumns(AddShared,{"employee", "accountmanager", "Shared"}),
    PivotManagers = Table.Pivot( SelectColumns,
        List.Distinct(SelectColumns[accountmanager]), "accountmanager", "Shared", List.Sum),
    AddTotal = Table.AddColumn( PivotManagers,
        "Total",
        each List.Sum ( List.Skip( Record.FieldValues(_) ) ),
        Currency.Type),
    GrandTotal = Table.FromColumns(
        {{"Grand Total"}} &amp;
        List.Transform( List.Skip( Table.ToColumns( AddTotal ) ),
            (q) =&gt; {List.Sum(q)} ), Table.ColumnNames(AddTotal)
    ),
    Result = AddTotal &amp; GrandTotal,
    DeclareType = Table.TransformColumnTypes( Result,
        List.Zip({ {Table.ColumnNames( Result ){0}}, {type text} } ) &amp;
        List.Transform( List.Skip( Table.ColumnNames( Result ) ), (q) =&gt; { q, Currency.Type } )
    )

in
    DeclareType

Forum Discussion

power query/ dax measure , multiple conditions/ multiple answers

6 Replies

Resources

category	revenue
EM01	86.000
EM02	68.000
EM03	93.000
EM04	87.000
EM05	6.000
EM06	96.000
EM07	87.000
EM08	54.000

employee	accountmanager	share accountmanager
EM01	AM1	75%
EM01	AM2	25%
EM02	AM1	100%
EM03	AM2	100%
EM04	AM3	100%
EM05	AM2	60%
EM05	AM3	40%
EM06	AM1	100%
EM07	AM1	50%
EM07	AM3	50%
EM08	AM2	100%

employee	AM1	AM2	AM3	total
EM01	64.500	21.500	-	86.000
EM02	68.000	-	-	68.000
EM03	-	93.000	-	93.000
EM04	-	-	87.000	87.000
EM05	-	3.600	2.400	6.000
EM06	96.000	-	-	96.000
EM07	43.500	-	43.500	87.000
EM08	-	54.000	-	54.000