cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
Helper II

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

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
Helper II
Helper II

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?

@JonasB

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

 

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

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

Helper II
Helper II

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

Skilled Sharer
Skilled Sharer

Hi @JonasB,

 

 

Have you tried to show values as a percentage?

Capture58.PNG

 

Hope it helps.

 

 

Regards

Abduvali

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

Super User IV
Super User IV

Can you post sample data?


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors