Summarize table dynamically based on field parameter

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