Find percentage of used budget by departments and in all

[Anonymous]

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

[Anonymous]

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?

[Eric_Zhang]

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

[Just2431]

Hello!

Is thisthe entire formula to create a measure ? 

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

Or else:

Percentage used = SUM(Spreadsheet1[Used]) / SUM(Spreadsheet1[Budget])  ??Or these are 2 diffrent formulas??when I type in the formula, i am getting en error after " ;" and addin next "SUM" . Thanks 

[Anonymous]

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

[Abduvali]

Hi @Anonymous,

Have you tried to show values as a percentage?

Hope it helps.

Regards

Abduvali

[Anonymous]

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

[Greg_Deckler]

Can you post sample data?