Dax formula to help display amounts when data is present in one set but not the other

[KN_HEALT47]

Hello,

Apologies for the wordiness.

Disclaimers:

(1) I am 100% self taught through trial and error which means sometimes I mess up jargon,  make things more complicated than they could be if I set up my data better, etc, etc (2) I tried to do some research on this topic prior to posting but I am struggling to find the words to descibe it in my search.

Context: 

I maintain a dashboard that has actuals and budget data. Actuals are direct queried from our accounting software and the budget is a static spreadsheet because our software does not house budget in the same desirable format as it does the actuals. There is demand at my company to see actuals vs budget comparison at very detailed levels. This was not a problem before because we budgeted at the account line (think something like software, rent, etc). I would then show the actuals all the way down to the vendor level and use ISINSCOPE() to make the value for budget blank when you drilled down further into the granularity of the actuals where budget didnt exist, example of the matrix below:

Problem:

For the budget this year we did things a little different and planned one level below that so now we have planned amounts at the individual vendor level. I was trying to have actuals AND budget populate when they intersected. The problem is that the only way I know how to to accomplish that is to create a table that has distinct names of all of our vendors that has to be updated anytime a transaction originates from a new vendor. This can work but the process is manual and the idea is to spend very little time refreshing this dashboard after we close the financials each period. You might be thinking why not just query the accounting software for all the vendors. Thats possible but would not account for new vendors we are planning for in the budget as actuals would not yet exist for them in our query.

Example of where the intermediary table got me in a matrix view:

*Notice the blank row occurs when a vendor does not exist in intermediary vendor table

Simplified version of the data connections:

Conclusion/Request:

Ideally I would not have the intermediary vendor table and could instead use a measure that will show the vendor when it exists in either budget or actuals and then make the value blank if it does not exist in the other dataset. I think this suggesting a many to many relationship which I understand can be tricky. This would make it so I do not have to keep the seperate vendor list. You may also notice that there is a blank row and thats what shows up when a vendor doesnt exist in the vendor only table. 

Example of what the ideal result would look like (the difference is subtle):

*Notice the vendor name is never blank even when it might not exist in one of the datasets. The amount is blank and thats ok.

Thanks in advance and please let me know if there is something I can clarify.

[v-yanjiang-msft · Accepted solution]

Hi @KN_HEALT47 ,

By my understanding, your company may have some new vendors, if the new vendor is not both in Actual and Budget tables, the Actual and Budget value should show blank.

In this case, here's the solution.

Create two measure for Actual and Budget.

Actual' =
IF (
    MAX ( 'Actual'[Vendor] )
        IN SELECTCOLUMNS ( 'Budget', "Vendor", 'Budget'[Vendor] ),
    MAX ( 'Actual'[Actual] ),
    BLANK ()
)

Budget' =
IF (
    MAX ( 'Budget'[Vendor] )
        IN SELECTCOLUMNS ( 'Actual', "Vendor", 'Actual'[Vendor] ),
    MAX ( 'Budget'[Budget] ),
    BLANK ()
)

Replace the values ​​of Actual and Budget with these two measures.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.