Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sharrold
Frequent Visitor

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:

SELECT
<OrgLevel1>,
<OrgLevel2>,
SUM([Revenue])
FROM [Data]
GROUP BY
<OrgLevel1>,
<OrgLevel2>
;

where the user can change `<OrgLevel1>` and/or `<OrgLevel2>` to be any of { "(All)", [Department], [Product] }.

 

The issue may be related to this post: https://community.powerbi.com/t5/Desktop/Calculated-Column-Table-Change-Dynamically-According-to-Sli...

 

Here's a link to a workbook that illustrates this issue, TestParameterizeGroupby.pbix (hosted by Google Drive). I've also included field definitions below with screenshots. Thanks for any help.

 

TestParameterizeGroupby.pbix:

Link: TestParameterizeGroupby.pbix (hosted by Google Drive)

 

Problem:

[Org Level 1] and [Org Level 2] fields are not recalculating from the users' selection. Only the default values are shown.

 
Expected result in table:
"Org Level 1","Org Level 2", "Revenue"
"(All)","(All)",28
 
Note:

The purpose is to have parameterizable organization level fields so that the report user can aggregate by all, department, product, or both in either order.

 
Table and column definitions:
'Data' = DATATABLE(
"Department",
STRING,
"Product",
STRING,
"Revenue",
DOUBLE,
{
{"DeptA", "ProdX", 5.0},
{"DeptA", "ProdY", 6.0},
{"DeptB", "ProdX", 10.0},
{"DeptB", "ProdY", 7.0}
}
)
'Data'[Org Level 1] = SWITCH(
'Org Level 1 Parameter'[Org Level 1 Parameter Value],
0,
"(All)",
1,
[Department],
2,
[Product]
)
// Problem: [Org Level 1] and [Org Level 2] fields are not recalculating from the users' selection. Only the default values are shown.
'Org Level 1' = DATATABLE(
"Org Level 1",
STRING,
"Org Level 1 Parameter",
INTEGER,
{
{"(0) (All)", 0},
{"(1) Department", 1},
{"(2) Product", 2}
}
)
'Org Level 1 Parameter'[Org Level 1 Parameter] = GENERATESERIES(0, 2, 1)
'Org Level 1 Parameter'[Org Level 1 Parameter Value] = SELECTEDVALUE('Org Level 1 Parameter'[Org Level 1 Parameter], 1)

Table 'Org Level 1' has a 1-1 relationship with 'Org Level 1 Parameter' on column [Org Level 1 Parameter].

The user selects the value for 'Data'[Org Level 1] by selecting the value for 'Org Level 1'[Org Level 1].

Tables and columns for [Org Level 2] are defined in the same way as [Org Level 1].

 
Screenshots:

Report:

ReportReport

 

Data:

DataData

 

 

 

Model:

ModelModel

 

Cross-reference to post in Stack Overflow: https://stackoverflow.com/questions/59686829/how-to-parameterize-a-column-for-aggregation-in-power-b...

1 ACCEPTED SOLUTION

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:

 

1151.png

 

For the related .pbix file, pls click here.

 

Best Regards,

Kelly

View solution in original post

4 REPLIES 4
v-kelly-msft
Community Support
Community Support

Hi @sharrold

 

Calculated columns can't be changed via your selection, if you wanna realize a dynamic value, you should create a measure instead of calculated column.

 

What you need is to create 3 measures as below:

Org Level 1M = SWITCH(
    'Org Level 1 Parameter'[Org Level 1 Parameter Value],
    0,
    "(All)",
    1,
    VALUES(Data[Department]),
    2,
    VALUES(Data[Product])
)
Org Level 2M = SWITCH(
    'Org Level 2 Parameter'[Org Level 2 Parameter Value],
    0,
    "(All)",
    1,
    VALUES(Data[Department]),
    2,
   VALUES(Data[Product])
)
RevenueM = SUMX('Data','Data'[Revenue])

Finally, you will see:

121.png

 

For the related .pbix file,pls click here.

 

Best Regards,

Kelly

 

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?

 

20200113_PowerBI_ProductDepartment_Error.png

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:

 

1151.png

 

For the related .pbix file, pls click here.

 

Best Regards,

Kelly

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:

ReportReport

Data view:

DataDataModel view: ModelModel

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.