andro02351
New Member
- Joined
- Jun 14, 2016
- Messages
- 11
I want to create/assign a new value in to some rows in my data depending on the average, maximum or minimum of an multiple criteria if based calculation on rows other than the current row.
I've created a regular xl file to illustrate where I want to go, however, I can't post attachments yet. In Excel of course I can't easily do the maxif or minif side at all. In Powerpivot I realise that this could be done with measures, however, I want the result to be a permanent addition to each record/row so would prefer a calculated field.
In regular excel I used the following formula (from my sample file attached) to return a field of averages, how do I do this in a Powerpivot table and how do I do it for maxif and minif. RealValues, Group, Number of Widgets and Has_a_Real_value being named single column ranges of the same dimensions as each other.
=IFERROR(IF(E4="Yes","",TRUNC(SUMIFS(RealValues,Group,A4,Number_of_Widgets,$C4,Has_a_Real_Value,"Yes")/COUNTIFS(Group,A4,Number of Widgets,Has_a_real_value,"Yes"),0)),"")
There will be some combinations that have no matches so the IFERROR handles those, most rows have a real value, so for those this calculation isn't needed and returns null. Another column will combine the assigned and real values into a single field, I will later manually assign values via a lookup table to those rows where the IFERROR was invoked.
Thanks
I've created a regular xl file to illustrate where I want to go, however, I can't post attachments yet. In Excel of course I can't easily do the maxif or minif side at all. In Powerpivot I realise that this could be done with measures, however, I want the result to be a permanent addition to each record/row so would prefer a calculated field.
In regular excel I used the following formula (from my sample file attached) to return a field of averages, how do I do this in a Powerpivot table and how do I do it for maxif and minif. RealValues, Group, Number of Widgets and Has_a_Real_value being named single column ranges of the same dimensions as each other.
=IFERROR(IF(E4="Yes","",TRUNC(SUMIFS(RealValues,Group,A4,Number_of_Widgets,$C4,Has_a_Real_Value,"Yes")/COUNTIFS(Group,A4,Number of Widgets,Has_a_real_value,"Yes"),0)),"")
There will be some combinations that have no matches so the IFERROR handles those, most rows have a real value, so for those this calculation isn't needed and returns null. Another column will combine the assigned and real values into a single field, I will later manually assign values via a lookup table to those rows where the IFERROR was invoked.
Thanks
