Forum Discussion

sharrold's avatar
sharrold
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:

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-Slicer/m-p/655991#M314800

 

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:

Report

 

Data:

Data

 

 

 

Model:

Model

 

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

  • 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

4 Replies

  • v-kelly-msft's avatar
    v-kelly-msft
    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:

     

    For the related .pbix file,pls click here.

     

    Best Regards,

    Kelly

     

    • sharrold's avatar
      sharrold
      Frequent Visitor

      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's avatar
        v-kelly-msft
        Community Support

        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