Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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 )
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
As you can see, if I slice it to 1 department, it says 100% even though it isn't
Hi @Anonymous,
Have you tried to show values as a percentage?
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 Jonas
Can you post sample data?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
81 | |
70 | |
62 |
User | Count |
---|---|
148 | |
116 | |
104 | |
90 | |
65 |