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 imported some data from excel and created a quick table in Power BI
How can I create a measure that calculates the percentage of the budget by each mean from the total?
Thanks
Solved! Go to Solution.
This has actually helped. Now resolved.
Drag budget again. In the fields pane, click on the arrow next to budget, choose show value as and then choose Percent of Grand total
Hi @Omega,
this helped for creation of the table, many thanks for that. Is there any way I could use these percentages in other visuals please?
Thanks,
You can apply same trick for other visuals 🙂
l've actually been splitting these number further by year and wanted to see a proprtional development YoY - to do this I used Show value as -> Percent of Column Total. This did the trick in the table.
Now, as 2019 has only just begun I cannot compare on the grand total with 2018 for the obvious reasons, but I still can compare how much budget goes to each mean proportionally. And I'd love to do this in a line chart (i.e. see development of Incentive going from 90% in 2018 to 60% in 2019), but there I cannot add the budget field second time, and show value as only offers Percent of Grand total, which again does not show what I need to see.
Am I being clear?
This has actually helped. Now resolved.
Hi @JurajSiska
Maybe something like
Measure = DIVIDE(SUM(Table1[Budget]), CALCULATE(SUM(Table1[Budget]), ALL(Table1)))
Hi @AlB ,
I used your formula and it worked like a charm.
do you think there's a way to make the (ALL(Table1)) part filter or axis?
I used this formula for my Employee engagement survey and i need to filter by question, country, age bracket and department.
so i need percent of total where total will be all of the above depending on the filter or Axis i set
Thanks.
Hi @Anonymous
If you are using for instance country in the axis, something like this should do it
Measure = DIVIDE(SUM(Table1[Budget]), CALCULATE(SUM(Table1[Budget]), ALL(Table1[Country])))
Same patter for all the others. You can also, instead of a measure, just place the column Budget in Values of your chart and choose to Show value as -> Percentage of grand Total
I would recommend measures in general but if you have too many fields to use in the axis, the latter might be a quicker option
Please mark the question solved when done and consider giving kudos if posts are helpful.
Cheers
Hi @AlB,
I'm getting an error saying the syntax is incorrect, any idea why? I havent been renaming anything.
Thanks
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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |