How to add dynamic rows with an average value based on a group of rows

[lafakios]

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

[lafakios]

Hi,

Would the fact that no more responses have been provided mean that this is not feasible to perform in PowerBI 🙂 ?

[lafakios]

Average 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_mayu]

where is the item 1.4 data? I can't see in it in your sample data.

[lafakios]

Thank you for building a PBIx report and sharing it, this actually does provide the needed functionality!

One of the key calculated columns is the below:

Column = CALCULATE(DISTINCTCOUNT('Table'[Item_ID]),FILTER('Table','Table'[param_id]=EARLIER('Table'[param_id])&&'Table'[item]=EARLIER('Table'[item])&&'Table'[Item_ID]>=EARLIER('Table'[Item_ID])))

I did a quick check if the above logic can scale to more than 4 items e.g. to support also items like itemA 1.5, 1.6, 1.7 etc. and the check result was successful.

One minor drawback of the current solution is that the column header in the visual refers to a measure name and not to the actual item represented by the measure e.g. itemA 1.4, 1.3, 1.2

[lafakios]

Hi,

In the shared report, I would like to add a Card visual which will display the number of params  per item that have a High or Low value. 

After experimenting a bit, this does not seem to be simple to implement.

Attempt 1

I have created the below measure and added in a card visual.

I have then added the measure 'Measure 3 HoL' as a card visual filter and I am not able to set its value. 

_CountOfParams = CALCULATE(DISTINCTCOUNTNOBLANK('Table'[param_id]))

Attempt 2

I have created the below measures and added then in two card visuals.

Both card visuals show exactly the same number, 5. This is the total number of params.

They should instead return respectively 3 and 2.

_CountOfHighParams = CALCULATE(DISTINCTCOUNTNOBLANK('Table'[param_id]), filter('Table','Table'[Measure 3 HoL] = "H"))

_CountOfLowParams = CALCULATE(DISTINCTCOUNTNOBLANK('Table'[param_id]), filter('Table','Table'[Measure 3 HoL] = "L"))

I have also updated the shared pbix file with the abovementioned measures and visuals and I am unfortunately not able to attach it in the post. I am just sharing a screenshot of the report page.

Any suggestions and ideas are more than welcome.

Regards,

Akis

[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

[ryan_mayu · Accepted solution]

@lafakios 

pls see the attachment below

[AmiraBedh]

Can you please share the logic of your calculations ? For HigherOrLowerThanAverage and Average of the last 3 ?