Forum Discussion

Frequent Visitor

6 years ago

Solved

How to parameterize a column for aggregation

I have users who would like to be able to modify what columns a table aggregates by. My issue is that I seem unable to do this in Power BI. I basically want to be able to do the following in SQL: SE...

Need Help

Tutorial Requests

v-kelly-msft

6 years ago

Hi sharrold ，

Measure should be corrected as below:

Result = 
SWITCH (
    SELECTEDVALUE ( 'Level 1 Table'[LevelName] ),
    "Department", SWITCH (
        SELECTEDVALUE ( 'Level 2 Table'[LevelName] ),
        "Department", CALCULATE (
            SUM ( 'Data'[Revenue] ),
            FILTER (
                'Data',
                [Department] IN FILTERS ( 'Level 1 Table'[LevelValue] )
                    && [Department] IN FILTERS ( 'Level 2 Table'[LevelValue] )
            )
        ),
        "Product", CALCULATE (
            SUM ( 'Data'[Revenue] ),
            FILTER (
                'Data',
                [Department] IN FILTERS ( 'Level 1 Table'[LevelValue] )
                    && [Product] IN FILTERS ( 'Level 2 Table'[LevelValue] )
            )
        ),
        CALCULATE (
            SUM ( 'Data'[Revenue] ),
            FILTER ( 'Data', [Department] IN FILTERS ( 'Level 1 Table'[LevelValue] ) )
        )
    ),
    "Product", SWITCH (
        SELECTEDVALUE ( 'Level 2 Table'[LevelName] ),
        "Department", CALCULATE (
            SUM ( 'Data'[Revenue] ),
            FILTER (
                'Data',
                [Product] IN FILTERS ( 'Level 1 Table'[LevelValue] )
                    && [Department] IN FILTERS ( 'Level 2 Table'[LevelValue] )
            )
        ),
        "Product", CALCULATE (
            SUM ( 'Data'[Revenue] ),
            FILTER (
                'Data',
                [Product] IN FILTERS ( 'Level 1 Table'[LevelValue] )
                    && [Product] IN FILTERS ( 'Level 2 Table'[LevelValue] )
            )
        ),
        CALCULATE (
            SUM ( 'Data'[Revenue] ),
            FILTER ( 'Data', [Product] IN FILTERS ( 'Level 1 Table'[LevelValue] ) )
        )
    ),
    SWITCH (
        SELECTEDVALUE ( 'Level 2 Table'[LevelName] ),
        "Department", CALCULATE (
            SUM ( 'Data'[Revenue] ),
            FILTER ( 'Data', [Department] IN FILTERS ( 'Level 2 Table'[LevelValue] ) )
        ),
        "Product", CALCULATE (
            SUM ( 'Data'[Revenue] ),
            FILTER ( 'Data', [Product] IN FILTERS ( 'Level 2 Table'[LevelValue] ) )
        ),
        SUM ( 'Data'[Revenue] )
    )
)

Finally you will see:

For the related .pbix file, pls click here.

Best Regards,

Kelly

sharrold

Frequent Visitor

6 years ago

Thanks v-kelly-msft for the fast response. Using a measure works for the case when [Org Level 1]="(All)", [Org Level 2]="(All)", but in the screenshot below, the workbook errors for other cases, such as when [Org Level 1]="Product", [Org Level 2]="Department". Do you have another suggestion?

v-kelly-msft

Community Support

6 years ago

Hi sharrold ，

Measure should be corrected as below:

Result = 
SWITCH (
    SELECTEDVALUE ( 'Level 1 Table'[LevelName] ),
    "Department", SWITCH (
        SELECTEDVALUE ( 'Level 2 Table'[LevelName] ),
        "Department", CALCULATE (
            SUM ( 'Data'[Revenue] ),
            FILTER (
                'Data',
                [Department] IN FILTERS ( 'Level 1 Table'[LevelValue] )
                    && [Department] IN FILTERS ( 'Level 2 Table'[LevelValue] )
            )
        ),
        "Product", CALCULATE (
            SUM ( 'Data'[Revenue] ),
            FILTER (
                'Data',
                [Department] IN FILTERS ( 'Level 1 Table'[LevelValue] )
                    && [Product] IN FILTERS ( 'Level 2 Table'[LevelValue] )
            )
        ),
        CALCULATE (
            SUM ( 'Data'[Revenue] ),
            FILTER ( 'Data', [Department] IN FILTERS ( 'Level 1 Table'[LevelValue] ) )
        )
    ),
    "Product", SWITCH (
        SELECTEDVALUE ( 'Level 2 Table'[LevelName] ),
        "Department", CALCULATE (
            SUM ( 'Data'[Revenue] ),
            FILTER (
                'Data',
                [Product] IN FILTERS ( 'Level 1 Table'[LevelValue] )
                    && [Department] IN FILTERS ( 'Level 2 Table'[LevelValue] )
            )
        ),
        "Product", CALCULATE (
            SUM ( 'Data'[Revenue] ),
            FILTER (
                'Data',
                [Product] IN FILTERS ( 'Level 1 Table'[LevelValue] )
                    && [Product] IN FILTERS ( 'Level 2 Table'[LevelValue] )
            )
        ),
        CALCULATE (
            SUM ( 'Data'[Revenue] ),
            FILTER ( 'Data', [Product] IN FILTERS ( 'Level 1 Table'[LevelValue] ) )
        )
    ),
    SWITCH (
        SELECTEDVALUE ( 'Level 2 Table'[LevelName] ),
        "Department", CALCULATE (
            SUM ( 'Data'[Revenue] ),
            FILTER ( 'Data', [Department] IN FILTERS ( 'Level 2 Table'[LevelValue] ) )
        ),
        "Product", CALCULATE (
            SUM ( 'Data'[Revenue] ),
            FILTER ( 'Data', [Product] IN FILTERS ( 'Level 2 Table'[LevelValue] ) )
        ),
        SUM ( 'Data'[Revenue] )
    )
)

Finally you will see:

For the related .pbix file, pls click here.

Best Regards,

Kelly

sharrold

Frequent Visitor

6 years ago

Thank you again v-kelly-msft for your solution.

I've cleaned up the file and reduced the necessary code in this version of your solution:

TestParameterizeGroupbySolution_PowerBI.pbix (hosted by Google Drive)

For posterity, here are my modifications of your definitions with screenshots:

(similar definition for 'Level 2' table)

Level 1 = UNION(
    DATATABLE(
        "Level 1",
        STRING,
        "Level 1 Value",
        STRING,
        {{"(All)", "(All)"}}
    ),
    CROSSJOIN(
        DATATABLE(
            "Level 1",
            STRING,
            {{"Department"}}
        ),
        SELECTCOLUMNS(
            DISTINCT('Data'[Department]),
            "Level 1 Value",
            [Department]
        )
    ),
    CROSSJOIN(
        DATATABLE(
            "Level 1",
            STRING,
            {{"Product"}}
            ),
        SELECTCOLUMNS(
            DISTINCT('Data'[Product]),
            "Level 1 Value",
            [Product]
        )
    )
)

Result = SWITCH(
    SELECTEDVALUE('Level 1'[Level 1])
    & " | " & SELECTEDVALUE('Level 2'[Level 2]),
    "(All) | (All)",
    SUM('Data'[Revenue]),
    "(All) | Department",
    CALCULATE(
        SUM('Data'[Revenue]),
        FILTER('Data', [Department] IN FILTERS('Level 2'[Level 2 Value]))
    ),
    "(All) | Product",
    CALCULATE(
        SUM('Data'[Revenue]),
        FILTER('Data', [Product] IN FILTERS('Level 2'[Level 2 Value]))
    ),
    "Department | (All)",
    CALCULATE(
        SUM('Data'[Revenue]),
        FILTER('Data', [Department] IN FILTERS('Level 1'[Level 1 Value]))
    ),
    "Department | Department",
    CALCULATE(
        SUM('Data'[Revenue]),
        FILTER(
            'Data',
            [Department] IN FILTERS('Level 1'[Level 1 Value])
            && [Department] IN FILTERS('Level 2'[Level 2 Value])
        )
    ),
    "Department | Product",
    CALCULATE(
        SUM('Data'[Revenue]),
        FILTER(
            'Data',
            [Department] IN FILTERS('Level 1'[Level 1 Value])
            && [Product] IN FILTERS('Level 2'[Level 2 Value])
        )
    ),
    "Product | (All)",
    CALCULATE(
        SUM('Data'[Revenue]),
        FILTER('Data', [Product] IN FILTERS('Level 1'[Level 1 Value]))
    ),
    "Product | Department",
    CALCULATE(
        SUM('Data'[Revenue]),
        FILTER(
            'Data',
            [Product] IN FILTERS('Level 1'[Level 1 Value])
            && [Department] IN FILTERS('Level 2'[Level 2 Value])
        )
    ),
    "Product | Product",
    CALCULATE(
        SUM('Data'[Revenue]),
        FILTER(
            'Data',
            [Product] IN FILTERS('Level 1'[Level 1 Value])
            && [Product] IN FILTERS('Level 2'[Level 2 Value])
        )
    )
)

Report view:

Report

Data view:

DataModel view: Model

Forum Discussion

How to parameterize a column for aggregation

Related content

Parameterizing a notebook

Single Selection of aggregated columns

Parameterizing connection data

Parameterized connections in Data pipelines

Data Aggregation issue