How to combine 2 report into 1

[sg0510]

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

[Anonymous]

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]

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

[v-ljerr-msft · Accepted solution]

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")

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

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

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.

Here is the sample pbix file for your reference. 

Regards

[sg0510]

Thank you so much v-ljerr-msft.

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

[Anonymous]

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