Forum Discussion
Helper IHow to add dynamic rows with an average value based on a group of rows
Helper IAverage 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}
Super Userwhere 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
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