Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
KriZo
Resolver II
Resolver II

Summarize based on field parameter

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

 

 

1 ACCEPTED SOLUTION
KriZo
Resolver II
Resolver II

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

 

View solution in original post

2 REPLIES 2
KriZo
Resolver II
Resolver II

@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
Super User
Super User

Hi @KriZo - 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!!

 

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

May 2025 Monthly Update

Fabric Community Update - May 2025

Find out what's new and trending in the Fabric community.