cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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:

 

Percentage used = DIVIDE(SUM(Spreadsheet1[Used]) ; SUM(Spreadsheet1[Budget]))

 

Or else:

 

Percentage used = SUM(Spreadsheet1[Used]) / SUM(Spreadsheet1[Budget])

 

Thank you for your answer though.

 

Regards Jonas

 

 

 

 

View solution in original post

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?

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

 

Percentage used = DIVIDE(SUM(Spreadsheet1[Used]) ; SUM(Spreadsheet1[Budget]))

 

Or else:

 

Percentage used = SUM(Spreadsheet1[Used]) / SUM(Spreadsheet1[Budget])

 

Thank you for your answer though.

 

Regards Jonas

 

 

 

 

Anonymous
Not applicable

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

 

To Power BI Forum - SS. - 2.PNG

Abduvali
Skilled Sharer
Skilled Sharer

Hi @Anonymous,

 

 

Have you tried to show values as a percentage?

Capture58.PNG

 

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 JonasTo Power BI Forum - SS.PNG

Greg_Deckler
Super User
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

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

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

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

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

Power Platform Conf 2022 768x460.jpg

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