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

Get 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

Reply
phuang
Frequent Visitor

Survey data has hierarchy question, interaction frequency then rating (weight) - help!

Hello experts,

 

I need help to transform one survey question into a line graph with a frequency filter.

 

Below is the survey question:

Survey question.JPG

Here's the collected data imported to Power BI:

data.JPG

 

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.

Line Graphic.JPG

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.  

unpivot all.JPG

 

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: 

rating table.JPG

Frequency table:

Frequency table.JPG

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!

 

1 ACCEPTED SOLUTION

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

10 REPLIES 10
Greg_Deckler
Super User
Super User

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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 UnitX FrequencyX PRODUCT RatingX ENGAGEMENT RatingY FrequencyY PRODUCT RatingY ENGAGEMENT RatingZ FrequencyZ PRODUCT RatingZ ENGAGEMENT Rating
A(Blank)(Select none)(Select none)   Monthly  
BYearly  Quarterly34Yearly33
CQuarterly33Yearly33Yearly33
DNever  Never     
EYearly22Never  Monthly33
FNever  Never  Quarterly22
G         
H      Monthly33
I         
JYearly32Yearly  Monthly33
K         
AYearly33Yearly33Yearly33
BYearly13Quarterly32Quarterly33
CNever  Yearly33Monthly44
DNever  Never  Quarterly33
DNever  Never  Yearly3 
C         
ENever33Never33Yearly33
F      Yearly33
INever  Never  Never  
JNever  Never  Quarterly33
KDaily22Yearly  Yearly2 

 

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Modified PBI file

Capture.JPG

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

Modified PBI file

Capture.JPG

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

Modified PBI file

Capture.JPG

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…

Modified PBI file

Capture.JPG

 

Thank you.

Penny

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Ashish,

 

It works, thank you for helping me on this 🙂

 

Penny 

 

 

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
October NL Carousel

Fabric Community Update - October 2024

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