Forum Discussion
Summarize based on field parameter
- Anonymous2 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
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)