Forum Discussion

Niels_83's avatar
Niels_83
Copper Contributor
Nov 17, 2025

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     ...
BI & Data Analysis
excel
Formulas and Functions
office 365
power bi
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Nov 17, 2025

Power Query variant with Grand Total

let
    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"}} &
        List.Transform( List.Skip( Table.ToColumns( AddTotal ) ),
            (q) => {List.Sum(q)} ), Table.ColumnNames(AddTotal)
    ),
    Result = AddTotal & GrandTotal,
    DeclareType = Table.TransformColumnTypes( Result,
        List.Zip({ {Table.ColumnNames( Result ){0}}, {type text} } ) &
        List.Transform( List.Skip( Table.ColumnNames( Result ) ), (q) => { q, Currency.Type } )
    )

in
    DeclareType
Niels_83's avatar
Niels_83
Copper Contributor
Nov 17, 2025

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.

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    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

Resources