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

Combined actual and target charts with a legend, please guide

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Combined actual and target charts with a legend, please guide

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

View solution in original post

3 REPLIES 3
Community Support Team
Community Support Team

Re: Combined actual and target charts with a legend, please guide

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
Mstrande Regular Visitor
Regular Visitor

Re: Combined actual and target charts with a legend, please guide

Tabel A with actuals  Tabel B with targets 
       
ProjectActuals (MSEK)Month ProjectActuals (MSEK)Month
A52018 01 A32018 01
A72018 02 A32018 02
A42018 03 A42018 03
A32018 04 A22018 04
A22018 05 A12018 05
A32018 06 A82018 06
B62018 01 B92018 01
B72018 02 B42018 02
B42018 03 B32018 03
B22018 04 B22018 04
B32018 05 B12018 05
B42018 06 B32018 06
C52018 01 C62018 01
C62018 02 C72018 02
C72018 03 C82018 03
C82018 04 C52018 04
C92018 05 C42018 05
C02018 06 C32018 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.

Community Support Team
Community Support Team

Re: Combined actual and target charts with a legend, please guide

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

View solution in original post

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 200 members 2,093 guests
Please welcome our newest community members: