Forum Discussion
Helper IHow to add dynamic rows with an average value based on a group of rows
Hi,
I would like to create a visual like the following one.
WANTED VISUAL
| Not To Be Shown | |||||||
| HigherOrLowerThanAverage | ItemA 1.4 | Average of the last 3 ItemA | ItemA 1.3 | ItemA 1.2 | ItemA 1.1 | ItemA 1.0 | |
| Param1 | L | 0 | 2 | 1 | 2 | 3 | 5 |
| Param2 | L | 9 | 14 | 12 | 14 | 16 | 12 |
| Param3 | L | 1 | 4 | 2 | 6 | 4 | 7 |
| Param4 | H | 6 | 3.33 | 3 | 3 | 4 | 12 |
| Param5 | H | 30 | 20 | 20 | 10 | 30 | 14 |
Considering the structure of the underlying data set (can be found at the bottom of the post), a matrix visual could be used for that purpose.
| Rows | Param_id |
| Columns | Item_ID |
| Values | value |
When doing so, it is not easy to add the columns highlighted in blue font color.
I have already spent some time trying to find ways to build the wanted visual. I have also experimented to pivot the param_id column in the data set. So far I have not been able to get to a solution.
Any advice or tips would be more than welcome.
Thanks in advance for your help!
Regards,
Akis
DATA SET
| Item_ID | param_id | value |
| ItemA 1.3 | Param1 | 1 |
| ItemA 1.3 | Param2 | 12 |
| ItemA 1.3 | Param3 | 2 |
| ItemA 1.3 | Param4 | 3 |
| ItemA 1.3 | Param5 | 20 |
| ItemA 1.2 | Param1 | 2 |
| ItemA 1.2 | Param2 | 14 |
| ItemA 1.2 | Param3 | 6 |
| ItemA 1.2 | Param4 | 3 |
| ItemA 1.2 | Param5 | 10 |
| ItemA 1.1 | Param1 | 3 |
| ItemA 1.1 | Param2 | 16 |
| ItemA 1.1 | Param3 | 4 |
| ItemA 1.1 | Param4 | 4 |
| ItemA 1.1 | Param5 | 30 |
| ItemA 1.0 | Param1 | 5 |
| ItemA 1.0 | Param2 | 12 |
| ItemA 1.0 | Param3 | 7 |
| ItemA 1.0 | Param4 | 12 |
| ItemA 1.0 | Param5 | 14 |
| ItemB 1.3 | Param1 | 20 |
| ItemB 1.3 | Param2 | 2 |
| ItemB 1.3 | Param3 | 14 |
| ItemB 1.3 | Param4 | 6 |
| ItemB 1.3 | Param5 | 3 |
| ItemB 1.2 | Param1 | 2 |
| ItemB 1.2 | Param2 | 14 |
| ItemB 1.2 | Param3 | 6 |
| ItemB 1.2 | Param4 | 3 |
| ItemB 1.2 | Param5 | 10 |
| ItemB 1.1 | Param1 | 3 |
| ItemB 1.1 | Param2 | 16 |
| ItemB 1.1 | Param3 | 4 |
| ItemB 1.1 | Param4 | 4 |
| ItemB 1.1 | Param5 | 30 |
| ItemB 1.0 | Param1 | 16 |
| ItemB 1.0 | Param2 | 4 |
| ItemB 1.0 | Param3 | 4 |
| ItemB 1.0 | Param4 | 30 |
| ItemB 1.0 | Param5 | 5 |
8 Replies
- AmiraBedh
Super User
Can you please share the logic of your calculations ? For HigherOrLowerThanAverage and Average of the last 3 ?
lafakiosAverage of the last 3 per param = {ItemA 1.3(param.value) + ItemA 1.2(param.value) + ItemA 1.1(param.value)} / 3
example: Average of the last 3 of param1 = {1 + 2 + 3} / 3 = 2
HigherOrLowerThanAverage per param = If( ItemA 1.4(param.value) >= Average of the last 3 per param, "H", "L")
example: HigherOrLowerThanAverage of param1 = L {0 < 2}
ryan_mayuwhere is the item 1.4 data? I can't see in it in your sample data.
- lafakios
The itemA 1.4 data were missing in the sample table.
Please find below the updated sample table including itemA 1.4
Item_ID param_id value ItemA 1.4 Param1 0 ItemA 1.4 Param2 9 ItemA 1.4 Param3 1 ItemA 1.4 Param4 6 ItemA 1.4 Param5 30 ItemA 1.3 Param1 1 ItemA 1.3 Param2 12 ItemA 1.3 Param3 2 ItemA 1.3 Param4 3 ItemA 1.3 Param5 20 ItemA 1.2 Param1 2 ItemA 1.2 Param2 14 ItemA 1.2 Param3 6 ItemA 1.2 Param4 3 ItemA 1.2 Param5 10 ItemA 1.1 Param1 3 ItemA 1.1 Param2 16 ItemA 1.1 Param3 4 ItemA 1.1 Param4 4 ItemA 1.1 Param5 30 ItemA 1.0 Param1 5 ItemA 1.0 Param2 12 ItemA 1.0 Param3 7 ItemA 1.0 Param4 12 ItemA 1.0 Param5 14 ItemB 1.3 Param1 20 ItemB 1.3 Param2 2 ItemB 1.3 Param3 14 ItemB 1.3 Param4 6 ItemB 1.3 Param5 3 ItemB 1.2 Param1 2 ItemB 1.2 Param2 14 ItemB 1.2 Param3 6 ItemB 1.2 Param4 3 ItemB 1.2 Param5 10 ItemB 1.1 Param1 3 ItemB 1.1 Param2 16 ItemB 1.1 Param3 4 ItemB 1.1 Param4 4 ItemB 1.1 Param5 30 ItemB 1.0 Param1 16 ItemB 1.0 Param2 4 ItemB 1.0 Param3 4 ItemB 1.0 Param4 30 ItemB 1.0 Param5 5
- lafakios
Hi,
Would the fact that no more responses have been provided mean that this is not feasible to perform in PowerBI 🙂 ?
