Difference Between Budget and Real - Two Datasets

[Anonymous]

Good Morning My Friends.

I'm new to PBI and did not find the solution to my problem seaching in the web.

I'll try to explain to you:

What I have are two differents Datasets, the first one contains the BUDGET of the Year and the second one the REAL(How much was spend).

The Budget Dataset Exemple:

Account	Month	$	Departament
1	Jan	100	DP1
1	Fev	200	DP1
1	Mar	100	DP1
2	Jan	300	DP1
2	Fev	250	DP1
3	Jan	320	DP2
4	Jan	180	DP2
4	Fev	200	DP2
5	Jan	170	DP2

So there are 5 differents departaments and each departament has a lot of differents accounts to manage. Each account has a budget to spend each month of the year.

The Real Dataset is how much was really Spend in each account, the difference is that the real dataset has a lot of differents payments checked in to the same Account.

Real Dataset Exemple:

Departament	Account	Month	$	Service
DP1	1	1	20	Fix Something
DP1	1	1	30	Buy Something
DP1	1	1	20	Pay Som
DP1	1	1	10	Buy Som
DP1	2	1	15	Fix Som
DP1	2	1	15	...
DP2	3	1	20	...
DP2	3	1	10	...
DP2	3	1	10	...
DP2	4	1	8	...
DP2	4	1	9	...
DP2	4	1	10	...
DP2	5	1	20	...
DP2	5	1	30	...

There is a different "Real" Dataset for each month. So I was able to merge them all up when we charge the dataset from a Folder. No problem there.

I also alredy did a Dynamic Calendar Dataset so I can use Power Pivot to link the tables.

The Problem:

What i need to do now is to do Real[$] - Budget[$] account by account so i know which account spent more than the Budget and which one has money remaining.

So what i thought to do was using DAX Summarize by Account and Month the "Real" Dataset and do a Measure Summarized_Real[$] - Budget[$].

I also thought to do a Table.Group using M to the Real Dataset, Grouping its values by Account and Month, and then try to Merge this Grouped Table with the Budget Table by Account and Month, than in a new Column in the Merged Table do the Math Operation....

But i do not know if its the better option.

All the Dashboard will use this "Difference" between budget and real... The idea is in the future show the top 5,10,15 accounts with more debt, the accounts which has more money. Do some charts with this values showing the Departaments by month and stuff like this.... So this will be an important "column"

Do you guys think that any of these solutions may work?

Is there anyway to do its easier?

Regards,

Thanks a lot!

[v-juanli-msft]

Hi @Anonymous 
Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case and help the other members find it more quickly?

If not, please feel free to let me know.

Best Regards

Maggie

[BA_Pete · Accepted solution]

Hi @Anonymous ,

I think your Table.Group in Power Query idea sounds fine. This is the way that I would solve the problem if I didn't need the extra detail from the Real table as it would push more calculation and processing away from the data model. I'd almost be tempted to put the variance column into Power Query as well, then just use a SUM([Variance]) measure to display the variance value.

Pete

[v-juanli-msft]

Hi @Anonymous 

Create a date table

date = FILTER(ADDCOLUMNS(CALENDAR(DATE(2020,1,1),TODAY()),"year",YEAR([Date]),"month",MONTH([Date]),"monthname",FORMAT([Date],"mmm")),DAY([Date])=1)

Create a measure

$ from budget =
CALCULATE (
    SUM ( 'Table 2'[$] ),
    FILTER (
        'Table 2',
        'Table 2'[Departament] = MAX ( 'Table 3'[Departament] )
            && 'Table 2'[Account] = MAX ( 'Table 3'[Account] )
            && 'Table 2'[month no] = MAX ( 'Table 3'[Month] )
    )
)

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.