Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
---|---|
109 | |
102 | |
84 | |
79 | |
70 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |