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