March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hello experts,
I need help to transform one survey question into a line graph with a frequency filter.
Below is the survey question:
Here's the collected data imported to Power BI:
I unpivoted only the rating columns and created below line graph but I don't know how to make the frequency filter works with the visual. The frequency-filter helps evaluate the data. An example could be if there is a correlation that higher scores come from individuals with more frequent interactions.
1st attempt:
I unpivoted the frequency column and the rating columns, but I couldn't figure out how to separate the frequency from the rating value so it won't affect the rating Avg. calculation.
2nd attempt:
I created two tables, I unpivoted only the frequency columns in one table and I unpivoted only the rating columns in another table but they don't interact with each other so the frequency filter doesn't work on the rating line graph.
Rating table:
Frequency table:
I watched many tutorials but still couldn't find the solution for this... Is there a way to pivot/unpivot twice so the data are in the same table which allows filtering? or is there a way to connect two tables and make the frequency filter works in the rating table?
Please help!
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
That is a lot to wade through. Can you post sample data as text, expected output and the logic of how you want to get from a to b? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Hi Greg,
Thanks for the guideline, I've read and put together below.
I expected to output line visual that shows the average rating of PRODUCT and ENGAGEMENT by X,Y, and Z which could be filtered by the Frequency (Daily, Weekly, Monthly, Quarterly, Yearly, Never). I want to know how the Frequency changes the Avg. rating.
I created the Avg. rating line visual by unpivoted the X, Y, Z PRODUCT/ENGAGEMENT columns but I don't know how to make the Frequency works with the visual. I tried unpivoted the Frequency columns with the PRODUCT and ENGAGEMENT columns but it messed up the Avg. rating calculation.
Business Unit | X Frequency | X PRODUCT Rating | X ENGAGEMENT Rating | Y Frequency | Y PRODUCT Rating | Y ENGAGEMENT Rating | Z Frequency | Z PRODUCT Rating | Z ENGAGEMENT Rating |
A | (Blank) | (Select none) | (Select none) | Monthly | |||||
B | Yearly | Quarterly | 3 | 4 | Yearly | 3 | 3 | ||
C | Quarterly | 3 | 3 | Yearly | 3 | 3 | Yearly | 3 | 3 |
D | Never | Never | |||||||
E | Yearly | 2 | 2 | Never | Monthly | 3 | 3 | ||
F | Never | Never | Quarterly | 2 | 2 | ||||
G | |||||||||
H | Monthly | 3 | 3 | ||||||
I | |||||||||
J | Yearly | 3 | 2 | Yearly | Monthly | 3 | 3 | ||
K | |||||||||
A | Yearly | 3 | 3 | Yearly | 3 | 3 | Yearly | 3 | 3 |
B | Yearly | 1 | 3 | Quarterly | 3 | 2 | Quarterly | 3 | 3 |
C | Never | Yearly | 3 | 3 | Monthly | 4 | 4 | ||
D | Never | Never | Quarterly | 3 | 3 | ||||
D | Never | Never | Yearly | 3 | |||||
C | |||||||||
E | Never | 3 | 3 | Never | 3 | 3 | Yearly | 3 | 3 |
F | Yearly | 3 | 3 | ||||||
I | Never | Never | Never | ||||||
J | Never | Never | Quarterly | 3 | 3 | ||||
K | Daily | 2 | 2 | Yearly | Yearly | 2 |
Please let me know if you'd like me to provide more information/details.
Thanks in advance for your time helping me.
Hi,
You may download the PBI file from here.
Hope this helps.
Hola Ashish,
Thanks a lot! It works perfectly! Your file really helps me 😊
I had spent many hours in this visual and felt so lost.
One more question, is there a way to add an additional filter for the PRODUCT/ENGAGEMENT rating?
I can only display the Average product by removing the average interaction rating from the values in the Display panel.
When I share my report, viewers won't have access to the display pane to make this change, so it would be easier if I provide it in the Filters pane. Viewers can see PRODUCT or ENGAGEMENT or both.
I played with your file and did it below:
Axis – Attribute.1 X,Y, Z – done
Filter – Business Unit – done
Product/Commitment – I'm not sure if this is feasible...
Thank you.
cent
Hola Ashish,
Thanks a lot! It works perfectly! Your file really helps me 😊
I had spent many hours in this visual and I felt so lost.
One more question, is there a way to add an additional filter for the PRODUCT/ENGAGEMENT classification?
I can only display the Average product by removing the average interaction rating of the values in the Display pane.
When I share my report, viewers won't have access to the display pane to make this change, so it would be easier if you provided it in the Filters panel. Viewers can watch PRODUCT or ENGAGEMENT or both.
I played with your file and I did it below:
Axis – Attribute.1 X,Y, Z – done
Filter – Business Unit – done
Product/Commitment – I'm not sure if this is feasible...
Thank you.
Penny
Hola Ashish,
Thanks a lot! It works perfectly! Your file really helps me 😊
I had spent many hours in this visual and I felt so lost.
One more question, is there any way to add an additional filter for the PRODUCT/ENGAGEMENT classification?
I can only display the Average product by removing the average interaction rating from the values in the Display panel.
When I share my report, viewers won't have access to the display pane to make this change, so it would be easier if I provided it in the Filters panel. Viewers can watch PRODUCT or ENGAGEMENT or both.
I played with your file and I did it below:
Axis – Attribute.1 X,Y, Z – done
Filter – Business Unit – done
Product/Commitment – I'm not sure if this is feasible...
Thank you.
Penny
Hi Ashish,
Thank you so much! It works perfectly! Your file really helps me out 😊
I had spent many hours on this visual and felt so lost.
One more question, is there a way to add an additional filter for PRODUCT / ENGAGEMENT rating?
I can show only the PRODUCT Avg. by deleting the Average engagement rating from the Values in the Visualization panel.
When I share my report, viewers won’t have access to the visualization panel to do this change, so it would be easier if I provide it in the Filters panel. Viewers can view either PRODUCT or ENGAGEMENT or both.
I played with your file and did below:
Axis – Attribute.1 X,Y, Z – done
Filter – Business Unit – done
Product/Engagement – not sure if this is doable…
Thank you.
Penny
Hi,
You may download my PBI file from here.
Hope this helps.
Ashish,
It works, thank you for helping me on this 🙂
Penny
You are welcome.
User | Count |
---|---|
122 | |
98 | |
89 | |
76 | |
68 |
User | Count |
---|---|
139 | |
115 | |
114 | |
98 | |
98 |