Calculating the % of budget used

[hessim]

Hi community

I am currently building a report for my personal financials. I am using SQL views as datasource.

One table contains dates, expenses, categories (FK) etc. and another table contains the categories themselves. a third table has the budget per year and category.

What I would like to calculate is a measure that allows me to use a bar chart that shows how much (%) of the budget has been used per category.

a date dimension table allows to filter per financial years.

I will update this post with the exact tables in a bit. 

Kind regards

Simon

[hessim]

Hi @v-yiruan-msft ,

thank you for your efforts. I have attached a PBIX file with cleaned up data. The bar chart you see currently holds the annual budgets (which I obviously dont want). What I want to show there is the percentage of budget used per category.

Each expense/reveneue is linked to one of the categories. The goals is the following:

1. Budget for category X is 1000

2. Expenses for that category were 600

3. Revenues for that category were 100

-> the bar chart should show 50% (1000-600+500 = 500) -> 50% budget left.

Thank you all for your help

[v-yiruan-msft · Accepted solution]

Hi @hessim ,

I updated your sample file, you can find the details in the attachment. Please check if that is what you want...

Expense = 
VAR _selcat =
    SELECTEDVALUE ( 'Jahresbudget'[Primärkategorie], "Hobbies" )
RETURN
    CALCULATE ( SUM ( 'Ausgaben'[Preis] ), 'Ausgaben'[Primärkategorie] = _selcat )

Revenue = 
VAR _selcat =
    SELECTEDVALUE ( 'Jahresbudget'[Primärkategorie], "Hobbies" )
RETURN
    CALCULATE ( SUM ( 'Einnahmen'[Preis] ), 'Einnahmen'[Primärkategorie] = _selcat )

Sparbetrag = [Revenue]-[Expense]

Sparquote % = [Expense]/[Revenue]

Remain % of budget = 
VAR _selcat =
    SELECTEDVALUE ( 'Jahresbudget'[Primärkategorie], "Hobbies" )
VAR _budget =
    CALCULATE (
        SUM ( 'Jahresbudget'[Budget] ),
        FILTER ( 'Jahresbudget', 'Jahresbudget'[Primärkategorie] = _selcat )
    )
RETURN
    1
        - DIVIDE ( _budget - [Expense] + [Revenue], _budget )

Best Regards

[v-yiruan-msft]

Hi @hessim ,

You can create a measure as below to get it:

Measure =
VAR _budget =
    CALCULATE ( SUM ( 'budgettable'[budget] ), ALL ( 'budgettable' ) )
VAR _actual =
    CALCULATE (
        SUM ( 'actualtable'[actual] ),
        FILTER (
            ALLSELECTED ( 'actualtable' ),
            'actualtable'[Date] <= SELECTEDVALUE ( 'Date'[Date] )
        )
    )
RETURN
    DIVIDE ( _actual, _budget )

In addition, you can refer the following links to get it.

Calculating Remaining budget

Find percentage of used budget by departments and in all

Finding The Percent Of Total In Power BI

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[hessim]

I updated the data model for easier reference