Show all budget values/periods

[yaman123]

Hi all, 

I have a table which is formed with a sql statement. There is table A (actual) which has accounting_period, accounting_year, actual_amount, cost centre etc.. 

This is then joined to table b (budget) which has columns cost_centre, accounting_period, accounting_year. 

The sql statement gives me budget results for periods which exist in table a. 

In Power BI, I have added a new table which gives me all my budget data from a spreadsheet with columns accounting_year, period, cost_centre, category, budget_value and i have linked this to the main table. But i can only see the budget periods if they exist in the main table.  I created a merged column in the actual table and a merged column in the budget table.  The columns merged are accounting year, period, category and cost centre. 

Is there a measure which will show all budget periods and values even if the period doesnt exist in the main table? 

[PaulDBrown · Accepted solution]

Use the following:

YTD Actuals =
CALCULATE (
    [Sum Actuals],
    FILTER (
        ALL ( 'Dim Period'[DPeriod] ),
        'Dim Period'[DPeriod] <= MAX ( 'Dim Period'[DPeriod] )
    )
)

YTD Budget =
CALCULATE (
    [Sum Budget Value],
    FILTER (
        ALL ( 'Dim Period'[DPeriod] ),
        'Dim Period'[DPeriod] <= MAX ( 'Dim Period'[DPeriod] )
    )
)

YTD Budget - Actuals = [YTD Budget] - [YTD Actuals]

for...

[yaman123]

@PaulDBrown thank you so much! I will play about with report and the visuals and see the results if they are what i need. I will let you know how i get on! 

[PaulDBrown]

You need to change the model to include dimension tables for period and category.

You also mention cost centre (which also needs a dimension table) but the field is missing in the sample data you have posted. Can you include it in the data &
can you post some sample data for the budget table please?

[yaman123]

Hi @PaulDBrown 

I have posted some sample data below. I have created a category table and linked it to the actual table but still no luck. The 'Other' ctaegory is showing but with no values? 

In Period 3, there is a value for category 'Other' but when i select period 4 from the slicer, nothing displays. If i select any period, this should show the YTD figure for actual and budget. 

Hope this helps!

[PaulDBrown]

Thanks for that but we need actual sample data (not an image) from both the fact and budget tables. You can create sample tables in excel and just copy and paste into your post or better yet, share a sample PBIX file through OneDrive, Google Drive, Dropbox etc...

[yaman123]

Hi @PaulDBrown 

Actual table sample data:

CATEGORY	ACCOUNTING_PERIOD	AMOUNT
Consultancy	1	£200.00
Consultancy	3	£500.00
Consultancy	4	-£500.00
IT Costs	1	£20,000.00
IT Costs	2	£21,000.00
IT Costs	3	£30,000.00
IT Costs	4	£40,000.00
IT Costs	5	£32,000.00
IT Costs	6	£2,000.00
Office Costs	1	£80.00
Office Costs	2	£80.00
Office Costs	4	£80.00
Office Costs	5	£80.00
Other	3	£275.00
Subscriptions	1	£0.00
Subscriptions	2	£40.00
Subscriptions	3	£0.00
Subscriptions	4	£2.00
Telephone & Mobile Phones	1	£1,000.00
Telephone & Mobile Phones	2	£2,000.00
Telephone & Mobile Phones	3	£3,000.00
Telephone & Mobile Phones	4	£4,000.00
Telephone & Mobile Phones	5	£5,000.00
Travel	1	£100.00
Travel	2	£100.00
Travel	3	£200.00
Travel	4	£500.00
Travel	5	£1,000.00

Budget table sample data:

CATEGORY	PERIOD	BUDGET_VALUE
Consultancy	1	£200.00
Consultancy	2	£200.00
Consultancy	3	£200.00
Consultancy	4	£200.00
Consultancy	5	£200.00
Consultancy	6	£200.00
Consultancy	7	£200.00
Consultancy	8	£200.00
Consultancy	9	£200.00
Consultancy	10	£200.00
Consultancy	11	£200.00
Consultancy	12	£200.00
IT Costs	1	£30,000.00
IT Costs	2	£30,000.00
IT Costs	3	£30,000.00
IT Costs	4	£30,000.00
IT Costs	5	£30,000.00
IT Costs	6	£32,000.00
IT Costs	7	£32,000.00
IT Costs	8	£32,000.00
IT Costs	9	£32,000.00
IT Costs	10	£32,000.00
IT Costs	11	£32,000.00
IT Costs	12	£32,000.00
Office Costs	1	£0.00
Office Costs	2	£0.00
Office Costs	3	£0.00
Office Costs	4	£0.00
Office Costs	5	£0.00
Office Costs	6	£0.00
Office Costs	7	£0.00
Office Costs	8	£0.00
Office Costs	9	£0.00
Office Costs	10	£0.00
Office Costs	11	£0.00
Office Costs	12	£0.00
Other	1	£0.00
Other	2	£0.00
Other	3	£0.00
Other	4	£0.00
Other	5	£0.00
Other	6	£0.00
Other	7	£0.00
Other	8	£0.00
Other	9	£0.00
Other	10	£0.00
Other	11	£0.00
Other	12	£0.00
Subscriptions	1	£120.00
Subscriptions	2	£120.00
Subscriptions	3	£120.00
Subscriptions	4	£120.00
Subscriptions	5	£120.00
Subscriptions	6	£120.00
Subscriptions	7	£120.00
Subscriptions	8	£120.00
Subscriptions	9	£120.00
Subscriptions	10	£120.00
Subscriptions	11	£120.00
Subscriptions	12	£120.00
Telephone & Mobile Phones	1	£3,000.00
Telephone & Mobile Phones	2	£3,000.00
Telephone & Mobile Phones	3	£3,000.00
Telephone & Mobile Phones	4	£3,000.00
Telephone & Mobile Phones	5	£3,000.00
Telephone & Mobile Phones	6	£3,000.00
Telephone & Mobile Phones	7	£3,000.00
Telephone & Mobile Phones	8	£3,000.00
Telephone & Mobile Phones	9	£3,000.00
Telephone & Mobile Phones	10	£3,000.00
Telephone & Mobile Phones	11	£3,000.00
Telephone & Mobile Phones	12	£3,000.00
Travel	1	£300.00
Travel	2	£300.00
Travel	3	£300.00
Travel	4	£300.00
Travel	5	£300.00
Travel	6	£0.00
Travel	7	£300.00
Travel	8	£1,500.00
Travel	9	£1,500.00
Travel	10	£1,500.00
Travel	11	£1,500.00
Travel	12	£0.00

[PaulDBrown]

Thanks for the data. It really helps!

Ok, so following modeling best practices, you need to set up the model with dimension tables for fields common to both fact tables. In your case, we need  dimension tables for period and category.

The model looks like this:

Now we can use the fields from the dimension tables in measures, slicer, filters, visuals etc. These fields from the dimension tables will then filter the relevant rows in each table to create the filter context.

So we can now create measures along the lines of:

Sum Actuals = SUM('Actual Table'[AMOUNT])

Sum Budget Value = SUM('Budget Table'[BUDGET_VALUE])

Act - Budget = [Sum Actuals] - [Sum Budget Value]

% vs budget = DIVIDE([Sum Actuals], [Sum Budget Value]) -1

Creating a matrix visual using the fields from the dimension tables and the measures, you get....

I've attached the sample PBIX file

[yaman123]

Hi @PaulDBrown 

Thanks for that. How would a YTD measure work for this? My current measure is 

Amount YTD =

var SelectedMonth =

MAX(Actual[ACCOUNTING_PERIOD])

RETURN

CALCULATE(

SUM(Actual[AMOUNT]),

FILTER(ALL(Actual[ACCOUNTING_PERIOD]),Actual[ACCOUNTING_PERIOD] <= SelectedMonth))


I would like to create a table which shows columns Category, YTD Actual Amount, YTD Budget Amount, Variance. I have added the 'Period' from the Period table to the slicer, 

[v-jingzhang]

Hi @yaman123 

Your description is not very clear. "But i can only see the budget periods if they exist in the main table." How do you see the data? If you select data fields into a table visual, did you select budget values/periods fields from Budget File table instead of Actual table into the visual? Do Budget File table have all budget values/periods but Actual table has only part of them?

Maybe you can share some sample data (removing sensitive info). This can help us understand the problem better. 

https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

Best Regards,
Community Support Team _ Jing

[yaman123]

That did not work. Here is some sample data. 

Category	Period	Amount
Consultancy	1	5000
Consultancy	2	4000
Other	3	275
Travel	1	100
Travel	2	150
Travel	3	100
Subscriptions	2	50
Subscriptions	3	50
Subscriptions	4	50

This is my YTD measure 

Amount YTD =

var SelectedMonth =

MAX(Actual[ACCOUNTING_PERIOD])

RETURN

CALCULATE(

SUM(Actual[AMOUNT]),

FILTER(ALL(Actual[ACCOUNTING_PERIOD]),Actual[ACCOUNTING_PERIOD] <= SelectedMonth))

I also have a budget table which is linked many to many and cross filter is both on merged columns. Merged columns in both tables are year, category, costcentre 

The budget table has all the categories and i am using this in the visual but still all categories are not showing with the YTD measures. 

I have a budget YTD measure too 

Budget YTD =

CALCULATE(

SUM('Budget File'[BUDGET_VALUE]),

FILTER(ALLEXCEPT('Budget File','Budget File'[CATEGORY]), 'Budget File'[PERIOD] <= MAX(Actual[ACCOUNTING_PERIOD])))

Hope this helps!

[PaulDBrown]

PLease follow the recommendations in this thread to help us help you:

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523