Summarize based on field parameter

[Anonymous]

Is there a way to have a column in a SUMMARIZE change based on a selected Field Parameter?

Essentially something like below. I know the code fails, just to show example of what i want to achieve. 

VAR __SelectedValue =
    SELECTCOLUMNS (
        SUMMARIZE ( 'Param', 'FP for Sup dev calculations'[Parameter], 'Param'[Parameter Fields] ),
        'Param'[Parameter]
    )
VAR _SelectedParam = IF ( COUNTROWS ( __SelectedValue ) = 1, MAX ( 'FP for Sup dev calculations'[Parameter] ), BLANK() )

VAR _Table = ADDCOLUMNS(SUMMARIZE(
Table, 
Table[Supplier], 
_SelectedParam
),
"total", SUM(Table[somevalue]))

[Anonymous · Accepted solution]

@rajendraongole1 

It seems to work. I did go with another option instead though. 

The reason i needed to do the SUMMARIZE with a field parameter was that I wanted to calculate and average from all rows based on the value in it's selected parameter so to speak. So i went with this, which worked as well
In below I essentially want to know that when Product Group is my selected parameter, I want to calculate Quartile values for what ever connection a row has to a specific product group, Sub Group when that's seleced and if non of those then for total supplier. 

Total Lines Included = 
VAR _SelectedParam =
    SELECTEDVALUE ( 'Parameter'[Parameter Order] )
VAR _Table =
    ADDCOLUMNS (
        SUMMARIZE (
            FactOrders,
            DimItem[Articlecode],
            DimItem[Product group],
            DimItem[Sub category],
            FactOrders[poNumber],
            FactOrders[lineNumber],
            FactOrders[supplierName],
            FactOrders[actualLeadTime]
        ),
        "Q3",
            SWITCH (
                _SelectedParam,
                0,
                    CALCULATE (
                        PERCENTILE.EXC ( FactOrders[actualLeadTime], 0.75 ),
                        ALLEXCEPT (
                            FactOrders,
                            FactOrders[supplierName],
                            DimItem[Product group]
                        )
                    ),
                1,
                    CALCULATE (
                        PERCENTILE.EXC ( FactOrders[actualLeadTime], 0.75 ),
                        ALLEXCEPT (
                            FactOrders,
                            FactOrders[supplierName],
                            DimItem[Sub category]
                        )
                    ),
                2,
                    CALCULATE (
                        PERCENTILE.EXC ( FactOrders[actualLeadTime], 0.75 ),
                        ALLEXCEPT (
                            FactOrders,
                            FactOrders[supplierName]
                        )
                    )
            ),
        "Q1",
            SWITCH (
                _SelectedParam,
                0,
                    CALCULATE (
                        PERCENTILE.EXC ( FactOrders[actualLeadTime], 0.25 ),
                        ALLEXCEPT (
                            FactOrders,
                            FactOrders[supplierName],
                            DimItem[Product group]
                        )
                    ),
                1,
                    CALCULATE (
                        PERCENTILE.EXC ( FactOrders[actualLeadTime], 0.25 ),
                        ALLEXCEPT (
                            FactOrders,
                            FactOrders[supplierName],
                            DimItem[Sub category]
                        )
                    ),
                2,
                    CALCULATE (
                        PERCENTILE.EXC ( FactOrders[actualLeadTime], 0.25 ),
                        ALLEXCEPT (
                            FactOrders,
                            FactOrders[supplierName]
                        )
                    )
            )
    )
VAR _FilteredTable =
    FILTER (
        _Table,
        FactOrders[actualLeadTime] >= [Q1] - ( 1.5 * [Q3] - [Q1] )
            && FactOrders[actualLeadTime] <= [Q3] + ( 1.5 * [Q3] - [Q1] )
    )
VAR _Rows = COUNTROWS(_FilteredTable)

RETURN
   IF(ISBLANK(_Rows),0,_Rows)

[rajendraongole1]

Hi @Anonymous - you need to create a field parameter in Power BI. This allows users to switch between different fields dynamically.

VAR __SelectedValue =
SELECTEDVALUE('FP for Sup dev calculations'[Parameter])

RETURN
SWITCH (
__SelectedValue,
"Parameter1", SUMMARIZE(Table, Table[Supplier], Table[Parameter1], "Total", SUM(Table[somevalue])),
"Parameter2", SUMMARIZE(Table, Table[Supplier], Table[Parameter2], "Total", SUM(Table[somevalue])),
"Parameter3", SUMMARIZE(Table, Table[Supplier], Table[Parameter3], "Total", SUM(Table[somevalue])),
// Add more cases as needed
BLANK()
)

Can you try the below measure  to change dynamically columns in a summarize based on the selected field parameter in Power BI.

DynamicSummarize =
VAR __SelectedValue =
SELECTEDVALUE('FP for Sup dev calculations'[Parameter])

RETURN
SWITCH (
TRUE(),
__SelectedValue = "Parameter1", CALCULATETABLE(
ADDCOLUMNS(
SUMMARIZE(Table, Table[Supplier], Table[Parameter1]),
"Total", SUM(Table[somevalue])
)
),
__SelectedValue = "Parameter2", CALCULATETABLE(
ADDCOLUMNS(
SUMMARIZE(Table, Table[Supplier], Table[Parameter2]),
"Total", SUM(Table[somevalue])
)
),
__SelectedValue = "Parameter3", CALCULATETABLE(
ADDCOLUMNS(
SUMMARIZE(Table, Table[Supplier], Table[Parameter3]),
"Total", SUM(Table[somevalue])
)
),
// Add more cases as needed
BLANK()
)

Try the above logic

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!