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

     

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