Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

8 REPLIES 8
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

 

 

 

 

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
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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.