Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Link: TestParameterizeGroupby.pbix (hosted by Google Drive)
[Org Level 1] and [Org Level 2] fields are not recalculating from the users' selection. Only the default values are shown.
"Org Level 1","Org Level 2", "Revenue"
"(All)","(All)",28
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.
'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].
Report:
Data:
Model:
Cross-reference to post in Stack Overflow: https://stackoverflow.com/questions/59686829/how-to-parameterize-a-column-for-aggregation-in-power-b...
Solved! Go to 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:
For the related .pbix file, pls click here.
Best Regards,
Kelly
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
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?
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
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:
Data view:
Model view:
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |