Forum Discussion
Summarize table dynamically based on field parameter
as1195 , First create a field parameter and create list
Use DAX to create a measure that dynamically groups and summarizes the sales values based on the selected fields.
DynamicSummary =
VAR SelectedFields = VALUES('FieldParameter'[Field])
VAR GroupedTable =
SWITCH(
TRUE(),
"Manufacturer" IN SelectedFields, SUMMARIZE('SalesTable', 'SalesTable'[Manufacturer], "TotalSales", SUM('SalesTable'[sales])),
"Manufacturer" IN SelectedFields && "Brand" IN SelectedFields, SUMMARIZE('SalesTable', 'SalesTable'[Manufacturer], 'SalesTable'[Brand], "TotalSales", SUM('SalesTable'[sales])),
"Manufacturer" IN SelectedFields && "Brand" IN SelectedFields && "SubBrand" IN SelectedFields, SUMMARIZE('SalesTable', 'SalesTable'[Manufacturer], 'SalesTable'[Brand], 'SalesTable'[SubBrand], "TotalSales", SUM('SalesTable'[sales])),
"Manufacturer" IN SelectedFields && "Brand" IN SelectedFields && "SubBrand" IN SelectedFields && "Variant" IN SelectedFields, SUMMARIZE('SalesTable', 'SalesTable'[Manufacturer], 'SalesTable'[Brand], 'SalesTable'[SubBrand], 'SalesTable'[Variant], "TotalSales", SUM('SalesTable'[sales])),
SUMMARIZE('SalesTable', 'SalesTable'[Manufacturer], "TotalSales", SUM('SalesTable'[sales]))
)
VAR TopResult = TOPN(1, GroupedTable, [TotalSales], DESC)
RETURN
TopResult
- Thanks bhanu_gautam ,
but this solution doesnt work since Var Grouped table wont be treated as table and we cannot use in topn function as table.
Also grouping of columns needs to be dynamic , as I have many fields in the parameter ,we need to add all permuatation and combination selection manually since the slicer is multiselect