cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
sg0510 Frequent Visitor
Frequent Visitor

How to combine 2 report into 1

Hi all,

 

 

We have a table named opportunity and now we want to show the result from 2 report into 1. The main table structure is like below

 

Opportunity ID | Opportunity Name | Expected Billing Date | Expected Order In Take Date | Amount

 

The first report, I used the column Expected Billing Date and Amount to form a Stacked column chart.

And the second report, I used the column Expected Order In Take Date and Amount to form another Stacked column chart.

 

My user request me to show these 2 stacked column charts into 1, so that they can compare the expected billing & expected order in report for each month.

 

Is it possible to perform on Power BI?

 

Thank you very much

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft v-ljerr-msft
Microsoft

Re: How to combine 2 report into 1

Hi @sg0510,

 

If I understand you correctly, you should be able to follow steps below to show Amount for both Expected billing date and Expected Order In Take date in a single chart.

 

1. Create an individual Date table if you don't have one yet.

Date = CALENDAR("2017/01/01","2017/12/31")

t1.PNG

 

2. Create two relationship between Opportunity table and Date table like below.

  • Expected billing date and Date (Active)
  • Expected Order In Take Date (Inactive)

relation1.PNG

 

3. Use the formulas below to create two measures to calculate Amount for Expected billing date and Expected Order In Take date separately.

BillAmount = SUM(Opportunity[Amount])
OrderAmount = 
CALCULATE (
    SUM ( Opportunity[Amount] ),
    USERELATIONSHIP ( Opportunity[Expected Order In Take Date], 'Date'[Date] )
)

4. Show the two measures as Values, and Date[Date] column as Axis on the Chart visual.

 

r1.PNG

 

Here is the sample pbix file for your reference. Smiley Happy

 

Regards

View solution in original post

5 REPLIES 5
LMeijdam Established Member
Established Member

Re: How to combine 2 report into 1

Hi @sg0510,

 

If you simply want to use 2 different data sources in one report that is possible in Power BI just import both the sources, and you should be able to use them both.

 

Here is a link for the documenation about multiple sources:

https://powerbi.microsoft.com/en-us/guided-learning/powerbi-learning-1-4-advanced-data-sources-and-t...

 

If you want to use both sources together in one visualisation for example you may need to create a relationship in the "relationship pane"

 

Here is a link for the documentation about relationships:

https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-create-and-manage-relationships/

 

Regards,

L.Meijdam

sg0510 Frequent Visitor
Frequent Visitor

Re: How to combine 2 report into 1

Thank you very much L.Meijgam.

 

I got your message clearly.

 

But if my 2 reports come from the same data source and same table, is it possible to present in 1 chart?

 

We need to do this because the amount is store in the opportunity table but they have 2 different date field for grouping

1. Expected billing date

2. Expected Order In Take date.

 

Thanks again

LMeijdam Established Member
Established Member

Re: How to combine 2 report into 1

Hi @sg0510,

 

I think that should be no problem if you add correct relationships, you could always try it first with a small sample dataset to test it.

 

L.Meijdam

Microsoft v-ljerr-msft
Microsoft

Re: How to combine 2 report into 1

Hi @sg0510,

 

If I understand you correctly, you should be able to follow steps below to show Amount for both Expected billing date and Expected Order In Take date in a single chart.

 

1. Create an individual Date table if you don't have one yet.

Date = CALENDAR("2017/01/01","2017/12/31")

t1.PNG

 

2. Create two relationship between Opportunity table and Date table like below.

  • Expected billing date and Date (Active)
  • Expected Order In Take Date (Inactive)

relation1.PNG

 

3. Use the formulas below to create two measures to calculate Amount for Expected billing date and Expected Order In Take date separately.

BillAmount = SUM(Opportunity[Amount])
OrderAmount = 
CALCULATE (
    SUM ( Opportunity[Amount] ),
    USERELATIONSHIP ( Opportunity[Expected Order In Take Date], 'Date'[Date] )
)

4. Show the two measures as Values, and Date[Date] column as Axis on the Chart visual.

 

r1.PNG

 

Here is the sample pbix file for your reference. Smiley Happy

 

Regards

View solution in original post

sg0510 Frequent Visitor
Frequent Visitor

Re: How to combine 2 report into 1

Thank you so much v-ljerr-msft.

 

You helped me to solve my problems and thanks again for your clear guideline and reference.Smiley Happy

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,121)