Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I want to do a line graph with following data:
Legend: Project: Project A, Project B
X axis: Month: 2018:1 for example
Y Axis MSEK
When I press Project A I would like to see the actuals compared to target by month for only that project.
Ive tried the following:
- Implement a second axis in the line graph. Seems not to work
- Use line and stacked column chart. But when I press a project the stacked columns doesnt only show that project, it shows all and the marked project is a little bit more colored. I only want the selected project to been seen.
The data is in two tables connected with a unique project name table.
I need to be able to show something by friday so I am really interested in good ideas.
Please guide
Solved! Go to Solution.
Hi @Mstrande,
You can refer to following link to know how to create a relationship between multiple columns.
Relationship in Power BI with Multiple Columns
After you finish build up relationship, you can simply use date and two value fields to create visualisations.
Detail steps:
1. Add calculate column M-P to raw tables.
MP= [Project]&"-"&[Month]
2. Create a bridge table with merged values to link to tables.
BRIDGE = ADDCOLUMNS ( DISTINCT ( UNION ( ALL ( TableA[Project], TableA[Month] ), ALL ( TableB[Project], TableB[Month] ) ) ), "MP", [Project] & "-" & [Month] )
3. Create visual with month as axis and two amount fields from different tables as values.
Regards,
Xiaoxin Sheng
Hi @Mstrande,
If you provide some sample data it will be help for test and coding formula.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
Tabel A with actuals | Tabel B with targets | |||||
Project | Actuals (MSEK) | Month | Project | Actuals (MSEK) | Month | |
A | 5 | 2018 01 | A | 3 | 2018 01 | |
A | 7 | 2018 02 | A | 3 | 2018 02 | |
A | 4 | 2018 03 | A | 4 | 2018 03 | |
A | 3 | 2018 04 | A | 2 | 2018 04 | |
A | 2 | 2018 05 | A | 1 | 2018 05 | |
A | 3 | 2018 06 | A | 8 | 2018 06 | |
B | 6 | 2018 01 | B | 9 | 2018 01 | |
B | 7 | 2018 02 | B | 4 | 2018 02 | |
B | 4 | 2018 03 | B | 3 | 2018 03 | |
B | 2 | 2018 04 | B | 2 | 2018 04 | |
B | 3 | 2018 05 | B | 1 | 2018 05 | |
B | 4 | 2018 06 | B | 3 | 2018 06 | |
C | 5 | 2018 01 | C | 6 | 2018 01 | |
C | 6 | 2018 02 | C | 7 | 2018 02 | |
C | 7 | 2018 03 | C | 8 | 2018 03 | |
C | 8 | 2018 04 | C | 5 | 2018 04 | |
C | 9 | 2018 05 | C | 4 | 2018 05 | |
C | 0 | 2018 06 | C | 3 | 2018 06 |
Hi, here is some data. I want to for a program be able to show 2 lines, one with targets and one with actuals. My workaround was to call programs with targets for A-target. Then I appended the tables into one. and then in Power BI select program A and program A-target. Not so nice but works.
Hi @Mstrande,
You can refer to following link to know how to create a relationship between multiple columns.
Relationship in Power BI with Multiple Columns
After you finish build up relationship, you can simply use date and two value fields to create visualisations.
Detail steps:
1. Add calculate column M-P to raw tables.
MP= [Project]&"-"&[Month]
2. Create a bridge table with merged values to link to tables.
BRIDGE = ADDCOLUMNS ( DISTINCT ( UNION ( ALL ( TableA[Project], TableA[Month] ), ALL ( TableB[Project], TableB[Month] ) ) ), "MP", [Project] & "-" & [Month] )
3. Create visual with month as axis and two amount fields from different tables as values.
Regards,
Xiaoxin Sheng
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |