Using the sum from two different reports in bar chart

[avou26]

Hello,

I have two different data sources that each have a list of payments and their corresponding dates( lets call them lake payments and park payments). I would like to have a bar chart that shows the two seperate totals per year in order to compare the lake payements sum and the park payments sum. the issue i am running into is that depending on the year field i use, either from lake payments or park payments, the totals change and only the total for the report that us having the date field used is correct. i have created a seperate calendar report but when i use that year field, it shows the same total for each year. Is there a way to have this show correctly?

Thank you in advance!

[Kedar_Pande · Accepted solution]

@avou26 

Yes, you can fix this issue by ensuring that both data sources (Lake Payments and Park Payments) are correctly linked to your calendar table.

Link the Date field in the Calendar table to the Date field in both the Lake Payments and Park Payments tables. Ensure both relationships are inactive so you can control them with DAX.

Measure 1:

LakePaymentsSum =
CALCULATE(
SUM('Lake Payments'[PaymentAmount]),
USERELATIONSHIP('Calendar'[Date], 'Lake Payments'[Date])
)

Measure 2:

ParkPaymentsSum =
CALCULATE(
SUM('Park Payments'[PaymentAmount]),
USERELATIONSHIP('Calendar'[Date], 'Park Payments'[Date])
)

• Use the Year field from the Calendar table for the axis.
• Use the two new measures (LakePaymentsSum and ParkPaymentsSum) for the values.

If this helped, a Kudos 👍 or Solution mark would be great!

Cheers,

Kedar Pande

www.linkedin.com/in/kedar-pande

[avou26]

This worked perfectly! Thank you !

[parry2k]

@avou26 seems like you are on the right path, set relationship from the calendar table with two other tables on the date column and then in the visualization use year from the calendar table and the sum value from the respective table and it will show sum from both the tables at the same axis.

[Jpss]

You definately need to use a calendar table. so that part is correct. 

However from what you have described,  it looks like the relationships are not set up correctly. Please make sure that the relationships are created between date table and both the fact tables
to help you better, it would be best If you can share a sample pbix file with sample dataset and relationships built in the data model, same way as in your report.