cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Find percentage of used budget by departments and in all

Hi,

I've tried all day to make a simply division like:

150/1000 = 0,15 * 100 = 15 %

Now, I got 11.000 rows with used budget and budget spreed on 7 departsments.

I want to have the possibility to see the percentage used for only 1 department compared to the whole budget, and only the total for that department and not all the underlying activities.

In a new colum i wrote: KPI = DIVIDE(spreadsheat1[Used];spreadsheet1[Budget];0)

When I do that and choose to slice it to 1 department, there will be like 2000 rows with at percentage, and a total percentage around 3000. The total percentage should only be like 15 % or so.

Can anyone help me?

Best regards Jonas

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi Eric_Zhang,

When I try to write your formular as a measure, i can't go any further than ... CALCULATE (.....

It can't find my spreadsheet strangely enough.

Anyway, I just got an answer from someone else, which is:

Or else:

Regards Jonas

7 REPLIES 7
Anonymous
Not applicable

Now I can see, that I wasn't precise in my formulation.

As you can see from the pictures, it shows a grand total of my budget for each department. I can't use that data for anything.

I want to see used budget in % for each department and used budget for for all the departments.

Can anyone help?

Microsoft

@Anonymous

Just try to create a measure instead of a calculated column as

```KPI =
DIVIDE (
SUM ( spreadsheat1[Used] ),
CALCULATE ( spreadsheet1[Budget], ALL ( spreadsheet1[Budget] ) ),
0
)```
Anonymous
Not applicable

Hi Eric_Zhang,

When I try to write your formular as a measure, i can't go any further than ... CALCULATE (.....

It can't find my spreadsheet strangely enough.

Anyway, I just got an answer from someone else, which is:

Or else:

Regards Jonas

Anonymous
Not applicable

As you can see, if I slice it to 1 department, it says 100% even though it isn't

Skilled Sharer

Hi @Anonymous,

Have you tried to show values as a percentage?

Hope it helps.

Regards

Abduvali

Anonymous
Not applicable

Thank you Abduvali, so far so good.

Now, if I slice to 1 department, it just says 100% under the colum %Gt KPI. Is it possibel that it still can show fx the 15,26 % if i choose fx AMA-adm as a department? Then I can use it as a KPI as I want to.

Beside, as you can see, it says that i've used 100 % of my budget even though i havent. Anyone know how to fix that, so i see the amount used in % by each department and as a grand total?

Regards Jonas

Super User

Can you post sample data?

@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

Announcements

#### The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

#### Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

#### Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors