Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
BruceAnderson
Helper II
Helper II

Compare actual with budget in two overate tables

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!

 

1 ACCEPTED SOLUTION
v-ljerr-msft
Employee
Employee

@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

actual.PNG

BUDGET table

budget.PNG

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

summarize.PNG

 

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]

column.PNG

 

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

report.PNG

Regards

View solution in original post

8 REPLIES 8
v-ljerr-msft
Employee
Employee

@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

actual.PNG

BUDGET table

budget.PNG

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

summarize.PNG

 

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]

column.PNG

 

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

report.PNG

Regards


@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?

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

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. 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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! 

Great to know how crossjoin works now.

quratzafar
Frequent Visitor

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
Advocate III
Advocate III

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

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.