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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Community Champion
Community Champion

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!!

 

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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