Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
lafakios
Helper I
Helper I

How 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
 HigherOrLowerThanAverageItemA 1.4Average of the last 3 ItemAItemA 1.3 ItemA 1.2ItemA 1.1ItemA 1.0
Param1L021235
Param2L91412141612
Param3L142647
Param4H63.3333412
Param5H302020103014

 

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. 

 

RowsParam_id
ColumnsItem_ID
Valuesvalue

 

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_IDparam_idvalue
ItemA 1.3 Param11
ItemA 1.3 Param212
ItemA 1.3 Param32
ItemA 1.3 Param43
ItemA 1.3 Param520
ItemA 1.2Param12
ItemA 1.2Param214
ItemA 1.2Param36
ItemA 1.2Param43
ItemA 1.2Param510
ItemA 1.1Param13
ItemA 1.1Param216
ItemA 1.1Param34
ItemA 1.1Param44
ItemA 1.1Param530
ItemA 1.0Param15
ItemA 1.0Param212
ItemA 1.0Param37
ItemA 1.0Param412
ItemA 1.0Param514
ItemB 1.3 Param120
ItemB 1.3 Param22
ItemB 1.3 Param314
ItemB 1.3 Param46
ItemB 1.3 Param53
ItemB 1.2Param12
ItemB 1.2Param214
ItemB 1.2Param36
ItemB 1.2Param43
ItemB 1.2Param510
ItemB 1.1Param13
ItemB 1.1Param216
ItemB 1.1Param34
ItemB 1.1Param44
ItemB 1.1Param530
ItemB 1.0Param116
ItemB 1.0Param24
ItemB 1.0Param34
ItemB 1.0Param430
ItemB 1.0Param55
1 ACCEPTED SOLUTION

@lafakios 

pls see the attachment below

 

11.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

8 REPLIES 8
lafakios
Helper I
Helper I

Hi,

 

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

lafakios
Helper I
Helper I

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}

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

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
 
HighLowParams.jpg

The itemA 1.4 data were missing in the sample table.

Please find below the updated sample table including itemA 1.4

 

Item_IDparam_idvalue
ItemA 1.4 Param10
ItemA 1.4 Param29
ItemA 1.4 Param31
ItemA 1.4 Param46
ItemA 1.4 Param530
ItemA 1.3 Param11
ItemA 1.3 Param212
ItemA 1.3 Param32
ItemA 1.3 Param43
ItemA 1.3 Param520
ItemA 1.2Param12
ItemA 1.2Param214
ItemA 1.2Param36
ItemA 1.2Param43
ItemA 1.2Param510
ItemA 1.1Param13
ItemA 1.1Param216
ItemA 1.1Param34
ItemA 1.1Param44
ItemA 1.1Param530
ItemA 1.0Param15
ItemA 1.0Param212
ItemA 1.0Param37
ItemA 1.0Param412
ItemA 1.0Param514
ItemB 1.3 Param120
ItemB 1.3 Param22
ItemB 1.3 Param314
ItemB 1.3 Param46
ItemB 1.3 Param53
ItemB 1.2Param12
ItemB 1.2Param214
ItemB 1.2Param36
ItemB 1.2Param43
ItemB 1.2Param510
ItemB 1.1Param13
ItemB 1.1Param216
ItemB 1.1Param34
ItemB 1.1Param44
ItemB 1.1Param530
ItemB 1.0Param116
ItemB 1.0Param24
ItemB 1.0Param34
ItemB 1.0Param430
ItemB 1.0Param55

@lafakios 

pls see the attachment below

 

11.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




AmiraBedh
Super User
Super User

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

 


Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Top Solution Authors