Summarize table dynamically based on field parameter

[as1195]

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

[Poojara_D12]

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()
    )

 

[OwenAuger]

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.

[OwenAuger]

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?

[bhanu_gautam]

@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

[as1195]

• 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