Compare actual with budget in two overate tables

[BruceAnderson]

I am trying to achieve what must be a common thing, but I am unable to succeed and cannot locate suitable info.

I have two tables:

(ACTUAL)                                              (BUDGET)

State   YYMM  Amount                         State   YYMM  Amount

QLD    16-05  100                                QLD    16-05  105

QLD    16-06  200                                QLD    16-06  202

NT       16-05  50                                  NT       16-05  60

NT       16-06  65                                  NT       16-06  45

 All I need to do is create a table showing the % of Budget VS Actual for each state for each month.

As it is a many to many relationship I am unsure how to link the two tables, or if I should be linking at all.

If I can get the linking right the DAX should be pretty straight forward.  Any suggestions much appreciated!

[v-ljerr-msft · Accepted solution]

@BruceAnderson

In addition to quratzafar's and osoosh's solutions, you can use CROSSJOIN function to join ACTUAL table and BUDGET table together into a new calculate table, then calculate % of Budget VS Actual within the new created table. The sample below is for your reference.

Assume we have ACTUAL table and BUDGET table like below.

ACTUAL table

BUDGET table

First, use the formula below to create a new calculate table called SummarizeTable.

SummarizeTable = 
FILTER (
    CROSSJOIN (
        SELECTCOLUMNS (
            ACTUAL,
            "Actual_State", ACTUAL[State],
            "Actual_YYMM", ACTUAL[YYMM],
            "Actual_Amount", ACTUAL[Amount]
        ),
        BUDGET
    ),
    [Actual_State] = [State]
        && [Actual_YYMM] = [YYMM]
)

Then, use the formula below to create a new calculate column to calculate the % of Budget VS Actual in SummarizeTable.

% of Budget VS Actual = SummarizeTable[Actual_Amount] / SummarizeTable[Amount]

Last, show the % of Budget VS Actual for each state for each month in report.

Regards

[gvg]

---

@v-ljerr-msft@v-ljerr-msft wrote:

SummarizeTable = 
FILTER (
    CROSSJOIN (
        SELECTCOLUMNS (
            ACTUAL,
            "Actual_State", ACTUAL[State],
            "Actual_YYMM", ACTUAL[YYMM],
            "Actual_Amount", ACTUAL[Amount]
        ),
        BUDGET
    ),
    [Actual_State] = [State]
        && [Actual_YYMM] = [YYMM]
)

 Can you please explain what [Actual_State] = [State] means here? Which tables it refers to?

[osoosh]

@gvg [Actual State] is from the Actual table and [State] is from Budget table. Hope this helps

[PaulDBrown]

Good evening,

I am trying to do something very similar, but related to actual sales and forecasted sales. The idea is to generate a visual in which a forecast may be made by inputting sales estimates for clients in future months (by month), and if there is no forecast for specific clients, then input the same sales as in the same month of the previous year for these clients. Therefore the result combines estimated sales for clients in which an estimate has been made, with last year's sales for those clients for which there is no estimate. (hope that doesn't sound too confusing...)

I find the solution using CROSSOIN interesting, but have a couple of questions:

a. Creating a new table involving potentially thousands or millions of rows, with calculated columns, should have a significant negative impact on the model's performance, right?

b. Does using CROSSJOIN actually include data for say "new clients"?  (ie. clients which did not buy during the previous year months under scrutiny, but which may now hava an estimate for future months).

In the model I'm working with, the sales data is in one table and the estimates are in another table. So far I have managed to build the model keeping the tables separate, but it sounds like CROSSJOIN actually "fuses" data from both tables into one, which from what I have read about building models should be avoided.

Is there another solution to this type of situation?

Many thanks for your help!

Best regards,

Paul. 

[BruceAnderson]

I was mustering the courage to attempt quratzafar's and osoosh's solutions, but then saw yours JerryLi.

With CROSSJOIN it was quite painless, and is working well.  Many thanks for all your help! 

[osoosh]

Great to know how crossjoin works now.

[quratzafar]

Hello, 

The way to resolve any many to many relationship is to split it into two one to many relations. In your case, simply create a new table by using 'summarize' on the state and then create one to many relationships with both tables. This should solve your problem. 

[osoosh]

Hi Bruce,

I had the same struggle before with the same issue. What I did was I created and extra table, say table state where it has a unique value. for this case QLD,NT. Relate both of them towards your actual table and budget table. Then relate your dimDate table towards your actual table for YYMM. However, you need to somehow connect budget table also to your dimDate table, therefore create a YEARMONTH column in your budget table which later will create a virtual relationship with a little bit of DAX involved. your dimDate should also have a YEARMONTH column. Now you can write a BudgetCalc measure as follow

BudgetCalc =
CALCULATE (
    SUM ( budget[Amount] ),
    FILTER (
        ALL ( budget[YEARMONTH] ),
        COUNTROWS (
            FILTER ( VALUES ( 'date'[YEARMONTH] ), 'date'[YEARMONTH] = budget[YEARMONTH] )
        )
            > 0
    ),
    FILTER (
        ALL ( budget[State] ),
        COUNTROWS ( FILTER ( VALUES ( state[State] ), state[State] = budget[State] ) )
            > 0
    )
)

You can finally compare Actual vs Budget by using State in State table which relates to both of actual and budget by states.

Hope it will help