cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
fjam0
Regular Visitor

Can this visualisation be archive? Line chart

Hello all users!

 

I requiere your help with this challenge! It is about a visualisation easy to create in Excel but I can´t find the way to replicate it with Power BI or maybe it isn't posible.

 

So with images I think it will be much easier:

  1. Lets say I measured, for males and females, the average hours of work for two days. So in Excel I can make a pivot table and organise the information as the image below:
  2. Untitled.png
  3. In the legend section of the pivot table I put gender and the values of hours of work are in the axis option. This permits that I can follow a gender by its line or color. Also, the values are in the average option.
  4. In Power BI I cant see the way to perform this type of row/column visualisation for Line Charts:
  5. Untitled3.png
  6. As you can see, the gender categories are in the X Axis and the hours of work corresponds to each color for males and females.
  7. Can I switch in someway this type of configuration of the Line Chart?
  8. I know, for example, that the matrix option has an row/column switch; can it be executed in a similar way?


I appreaciatte every comment or suggestion.

Regards,

F.

1 ACCEPTED SOLUTION

@fjam0  You need to unpivot REC1 and 2 together, in the same step (use Ctrl key or Shift key to select them together and unpivot). So your data should look like this when you're done: 

 

 

GeneroCargoFRanquiciaNombre PDVCatValue
Fem   REC15
Fem   REC25
Fem     

 

You still have two columns for REC1 and Cat, but need only 1 column for Cat that lists the REC1 AND REC2 with the value in the column next to it. 

 

If this still doesn't make sense, can you post what your data looks like before the unpivot please?

 

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv


Check out my Tokyo 2020 report with live stats - KUDOS much appreciated



Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos. ?


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

View solution in original post

7 REPLIES 7
Greg_Deckler
Super User IV
Super User IV

You need your Genero (Gender) in the Legend just like you have it in your Excel line cart. It's in the Legend.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




Hello @Greg_Deckler ,

 

Thank you for you super fast reply. Unfortunately, PowerBI doesnt let me put Gender on the legend option. Thus, I can upload my pbix file if it's more easy to view.

 

 

Regards,

F.

@fjam0  Thanks for the pictures - they're helpful. From what I see, you have two fields in the values, REC1 and REC2. You cannot use both a legend and two values, only one OR the other.

 

If you are wanting these displayed as category axis, then they need to be unpivoted so that you have a table that looks like this:

GenderCategoryAverage
MaleREC14.3
FemaleREC1

4.4

MaleREC2

4.4

FemaleREC2

4.4

 

To do this, click Transform Data to open the Query Editor, navigate to your data table that you're trying to graph, and select all the REC values, then click Unpivot (or better way might be to select all the columns that are NOT REC values and click Unpivot other columns, just depends on how much your data will change from Excel). 

Then rename the 'Attribute' to 'Category' and 'Values' to 'Average' or whatever names you prefer.

 

Once you've done that, you can put Category in Category, Gender in Legend and Average in values.

 

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv


Check out my Tokyo 2020 report with live stats - KUDOS much appreciated



Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos. ?


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

Hello @AllisonKennedy .

 

Thank you so much for your time and great answer.

 

I tried what you say and I have partially solved the issue.

  1. I unpivoted the columns that you told me and I got the table:
    1. Untitledtable.png
  2. So I got Gender, Category, and Values or average, etc.
  3. Then I proceed to do the line chart:
    1. graph2.png
  4. I succesfully can now illustrate the first of the values, with the category on Axis, Gender on Legend and Rec1 on values.
  5. From there, I the only thing left is to be able to include REC2 in the values, but I dont know if this is possible through this way. When I click it, it goes to tooltip section and it only show.
  6. The final goal for me is to be able to visualise, for males and females, REC1 and REC2 as a line chart.

 

Thanks again Allison

 

Regards,

F.

@fjam0  You need to unpivot REC1 and 2 together, in the same step (use Ctrl key or Shift key to select them together and unpivot). So your data should look like this when you're done: 

 

 

GeneroCargoFRanquiciaNombre PDVCatValue
Fem   REC15
Fem   REC25
Fem     

 

You still have two columns for REC1 and Cat, but need only 1 column for Cat that lists the REC1 AND REC2 with the value in the column next to it. 

 

If this still doesn't make sense, can you post what your data looks like before the unpivot please?

 

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv


Check out my Tokyo 2020 report with live stats - KUDOS much appreciated



Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos. ?


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

View solution in original post

Hello @AllisonKennedy ,

 

 

Brilliant! It worked just fine now. I can't tell you how much time i spend on this 🤗!

 

graph.png

 

 

Thank you very much!

 

Kind Regards,

Francisco Javier Acuña

fjam0
Regular Visitor

UP!🤗 

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors