Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
Solved! Go to Solution.
pls see the attachment below
Proud to be a Super User!
Hi,
Would the fact that no more responses have been provided mean that this is not feasible to perform in PowerBI 🙂 ?
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.
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:
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.
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.
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 |
pls see the attachment below
Proud to be a Super User!
Can you please share the logic of your calculations ? For HigherOrLowerThanAverage and Average of the last 3 ?