Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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

 

 

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    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)

     

2 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    2 years ago

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

     

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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)