topic Re: Summarize table dynamically based on field parameter in DAX Commands and Tips

Summarize table dynamically based on field parameter

as1195 — Wed, 29 Jan 2025 04:48:30 GMT

Hi,

I have field parameter with manufacturer,brand ,subbrand,variant fields with multiselect option
I want to create dax which will group sales values based on field selected and should return top 1 value.
I am trying to create summarize table within dax but dynamic grouping of columns based on field paramerer selection is not working .Any help in this regard would be much appreciated.
For example
If user selects manufacturer the dax should return XYZ as result
if user selects Manufacturer and brand then result should return Abc-a and so on

Manufacturer	Brand	SubBrand	Variant	sales
Abc	a	aa	a1	1000
XYZ	b	ba	b1	2000
Abc	a	ab	a2	1200

Re: Summarize table dynamically based on field parameter

bhanu_gautam — Wed, 29 Jan 2025 05:23:02 GMT

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

Re: Summarize table dynamically based on field parameter

as1195 — Wed, 29 Jan 2025 05:57:35 GMT

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

Re: Summarize table dynamically based on field parameter

OwenAuger — Thu, 30 Jan 2025 04:38:29 GMT

Hi as1195

Does Manufacturer > Brand > SubBrand > Variant form a "natural hierarchy"? It appears so from your sample data at least 🙂

That is, does any value at a particular level imply a unique value at the level immediately above?

i.e. each Variant exists under a unique SubBrand, each SubBrand exists under a unique Brand etc?

If it is a natural hierarchy, I believe it is possible and I can put together an example. If it is not a natural hierarchy, you could still adjust the model to create a natural hierarchy behind the scenes.

Regardless, I will put together an example based on what I am thinking and reply soon. The main difficulty is that field parameters are intended to determine fields included in a visual, but not to generate dynamic DAX expressions using those fields.

Re: Summarize table dynamically based on field parameter

Poojara_D12 — Thu, 30 Jan 2025 05:09:12 GMT

Hi as1195

To solve the dynamic grouping issue in Power BI using DAX, you need a measure that adjusts based on the selected field parameter (e.g., Manufacturer, Brand, SubBrand, or Variant). The DAX formula uses SELECTEDVALUE to capture the field selected by the user. The SWITCH function is used to dynamically choose the grouping field and calculate the total sales for each group using SUMMARIZE. Then, the TOPN function returns the top 1 value based on sales in descending order, ensuring that the result reflects the highest sales group. Finally, MAXX extracts the total sales value of the top result. This measure works for any dynamic combination of fields selected, such as Manufacturer alone or Manufacturer and Brand together. It ensures the correct top value is returned based on user input, offering flexibility in grouping and analysis.

Updated DAX:

Top Sales Based on Selection = VAR SelectedField = SELECTEDVALUE('FieldParameter'[Field]) -- get the selected field parameter value VAR GroupedData = SWITCH( TRUE(), SelectedField = "Manufacturer", SUMMARIZE('SalesData', 'SalesData'[Manufacturer], "TotalSales", SUM('SalesData'[Sales])), SelectedField = "Brand", SUMMARIZE('SalesData', 'SalesData'[Brand], "TotalSales", SUM('SalesData'[Sales])), SelectedField = "SubBrand", SUMMARIZE('SalesData', 'SalesData'[SubBrand], "TotalSales", SUM('SalesData'[Sales])), SelectedField = "Variant", SUMMARIZE('SalesData', 'SalesData'[Variant], "TotalSales", SUM('SalesData'[Sales])), BLANK() ) VAR TopValue = TOPN(1, GroupedData, [TotalSales], DESC) -- Top 1 value based on sales RETURN IF( NOT ISBLANK(TopValue), MAXX(TopValue, [TotalSales]), BLANK() )

Re: Summarize table dynamically based on field parameter

OwenAuger — Wed, 12 Feb 2025 02:02:58 GMT

Hi as1195

I'm not sure if you're still looking for a solution to this, but I have attached a PBIX showing the method I was thinking of.

This is based on the sample dataset from your original post.

1. Model diagram:

2. Product table

Product is a typical product table with a Product Key column added.

For this method to work, the assumption is that Manufacturer>Brand>SubBrand>Variant forms a natural hierarchy.

This means that the "deepest" level of the hierarchy maps 1:1 to the combinations of values on all levels of the hierarchy.

3. ProductAttributeValue table

This is created by unpivoting all columns of Product except Product Key, and adding a Depth column.

ProductAttributeValue[Attribute] is related to 'Product Parameter'[Product Parameter], so that when particular fields are selected, the visible rows of ProductAttributeValue include those levels of the Product hierarchy.

4. Top Attribute Combination measure

This measure assumes you have a Sales Amount measure, and uses the above model to return the result you were looking for:

Top Attribute Combination = VAR MaxDepthSales = CALCULATETABLE ( ADDCOLUMNS ( SUMMARIZE ( ProductAttributeValue, ProductAttributeValue[Value], ProductAttributeValue[Depth] ), "@Sales", [Sales Amount] ), LASTNONBLANK ( ProductAttributeValue[Depth], 0 ) -- Filter on max depth ) VAR MaxDepthTopAttribute = INDEX ( 1, MaxDepthSales, ORDERBY ( [@Sales], DESC, ProductAttributeValue[Value], ASC ) -- break ties lexicographically ) VAR MaxDepthTopAttributeProducts = CALCULATETABLE ( VALUES ( ProductAttributeValue[Product Key] ), MaxDepthTopAttribute ) VAR AttributeConcatenation = CALCULATE ( CONCATENATEX ( SUMMARIZE ( ProductAttributeValue, ProductAttributeValue[Value], ProductAttributeValue[Depth] ), ProductAttributeValue[Value], "-", ProductAttributeValue[Depth] ), MaxDepthTopAttributeProducts ) RETURN AttributeConcatenation

5. Report example

I realise this requires a little setup, but I can't see any other method of producing this result.

Conditional tables or tables with conditional lineage are not possible, so there is no way to directly map the field parameter selection to column references within DAX.

Does something like this work for you?