percentage from total

[JurajSiska]

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

[Omega]

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 

[JurajSiska]

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,

[Omega]

You can apply same trick for other visuals 🙂

[JurajSiska]

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?

[JurajSiska · Accepted solution]

This has actually helped. Now resolved.

https://www.fourmoo.com/2017/07/18/power-bi-dax-measures-for-excel-based-of-column-total-or-of-row-t...

[AlB]

Hi  @JurajSiska

Maybe something like

Measure = DIVIDE(SUM(Table1[Budget]), CALCULATE(SUM(Table1[Budget]), ALL(Table1))) 

[Anonymous]

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.

[AlB]

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 

[JurajSiska]

Hi @AlB,

I'm getting an error saying the syntax is incorrect, any idea why? I havent been renaming anything.

Thanks