How to make two visuals (bar chart) showing in same ratio (using data from different data sources)

[JustinDoh1]

Hi Community,

I am sharing my Pbix file here.

I am trying to show bar chart graphs from two different data sources.

I am putting one visual next to other visual because one is showing Actual and the other one is Estimate (shown below).

I was not able to combine these two datasets into one table, so I had to display like this way.

The issue is whenever there is a negative number showing on one graph, the proportion of other graph does not go along with the other chart's number & ratio.

Here is an example for the selection for two slicers:

- Location = 100

- Payer = Hospice

How do we fix this issue?

Thanks.

[davehus · Accepted solution]

Hi, I've approached this a little differenlty. I've created a dimensional table, called BudgetType and joined into your fact tables on the calulcated columns.

I've written the measure below then to bring both values into the same visual.

Test = IF(FIRSTNONBLANK(Saga[ActualOrEstimated2],1) = "Actual",[TotalGL],if(FIRSTNONBLANK(PDF[ActualOrEstimated2],1) = "Estimate",[Total],BLANK()))

You will get the resulting visual. 

It needs more testing from you end as you understand the model better, but it may suit your needs.

PBIX https://drive.google.com/file/d/1p5rT7A5bm7CEoG-AdAZVo6Y49VjoEB1Y/view?usp=sharing

[JustinDoh1]

@davehus 

Thank you so much for your help.

Your model is definitely smart.

I am also thinking about other approach for a solution: join two datasets on Date columns (BatchDate and TransactionDate).

Then, probably apply the measure to determine Actual and Estimate (because logic to determine Actual and Estimate is same for both datasets), at least that is where I am leaning toward.

The reason is that all months for Saga only belong to Actual, and all months for PDF only belong to Estimate.  Maybe your model got that logic right anyway (sorry, I need to analyze your model more closely).

My issue now is that two datasets come from different SQL databases so, I need to figure out how to combine two tables in PowerBI.

If I try to do it in PowerBI, I get many to many relationship, and I am not sure that is ok.

This is what I am trying to do:

I guess, my challenge is, how do I display the data on columns?

I updated my Pbix file with a relationship between two dates.