Forum Discussion
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-msftCommunity 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
- sharroldFrequent 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-msftCommunity 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