Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I am quite new to PowerBI and I am struggling with the following problem:
I have a table (Table 1) containing company names and general information. The companies (there are thousands of them) could change over time. It looks like this:
Name | Sector | Info |
Apple | Consumer Products | Phones |
Software | Search | |
Pizza Hut | Food | Pizzas |
… (thousands of comps) | … | … |
I have another table (Table 2) that contains a time-series table about the market capitalization of each company:
Date | Apple | Pizza Hut | … (tousands of comps) | |
01.01.2001 | 100 | 70 | 10 | … |
02.01.2001 | 110 | 80 | 20 | … |
03.01.2001 | 120 | 90 | 30 | … |
04.01.2001 | 130 | 100 | 40 | … |
… | … | … | … | … |
What I would like to achieve is the following: I would like to plot the market cap from Table 2 in a line plot over time. Then I would like to have a slicer next to the plot, where I can select the companies. Something like in this mockup:
Is this possible in PowerBI? Can I do it with some dynamic measures? Do I need to create custom visuals?
Best regards
-Sebastian
Solved! Go to Solution.
Best solution is to unpivot your columns from table 2. Go to query Editor for Table 2 > Transform.
Click on Date column and select "Unpivot other columns" i.e. it will unpivot all the other columns except Date (which is the selected column).
Rename the Attribute column and then join it with Table1.
On your line chart, add the attribute as a Legend. Then configure your slicer to allow multiple selection. They will come as separate chart
Best solution is to unpivot your columns from table 2. Go to query Editor for Table 2 > Transform.
Click on Date column and select "Unpivot other columns" i.e. it will unpivot all the other columns except Date (which is the selected column).
Rename the Attribute column and then join it with Table1.
OK, that's almost what I want. Thanks!
My table (Table 2) now looks like this (So far, I did not join Table 1 and Table 2):
Date | Companies | Market Cap |
01.01.2001 | Apple | 100 |
01.01.2001 | 70 | |
01.01.2001 | Pizza Hut | 10 |
02.01.2001 | Apple | 110 |
02.01.2001 | 80 | |
02.01.2001 | Pizza Hut | 20 |
… | … | … |
When I plot my line plot, I get a good result only when I chose one company in the slicer. As soon as I chose 2 companies, PowerBI seems to show the sum of both companies (see 'Actual Result'):
What would be your recommendation to achieve the 'desired result', i.e. showing separate plots with different colors for all selected companies?
On your line chart, add the attribute as a Legend. Then configure your slicer to allow multiple selection. They will come as separate chart
Thank you! That is exactly what I was looking for.
Could you put an image of how the graph looked?
Tanks!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |