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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Mstrande
Resolver I
Resolver I

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

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
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

3 REPLIES 3
v-shex-msft
Community Support
Community Support

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
If this post helps, please consider accept as solution to help other members find it more quickly.

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.

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
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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