# Percentage computation in Power BI Designer



## cyrilbrd (Jul 15, 2015)

Given in Query a whole number Column representing volume produced, given another text Column giving locations
I tried to get the percentage of volume per location.
Thought of vol_produced = SUM(table[TOTAL HARVEST])/ALL(table[TOTAL HARVEST]) but this does not work.
Any idea?


----------



## Matt Allington (Jul 16, 2015)

try this. 
Thought of vol_produced = SUM(table[TOTAL HARVEST])/calculate(sum(table[TOTAL HARVEST]) , all(table))

Its hard to be sure without seeing the table.


----------



## cyrilbrd (Jul 16, 2015)

Matt Allington said:


> ...
> Thought of vol_produced = SUM(table[TOTAL HARVEST])/calculate(sum(table[TOTAL HARVEST]) , all(table))
> 
> ...



Nope it works fine, tried to look for some answers in PowerPivotPro (Rob) but found quickly drown to the pit of being utterly lost...
Note although the data I wanted is properly returned by CALCULATE(SUM(Column_of_Interest), 'all' as a filter) I noticed that Power BI Designer is not displaying the data as a percentage, but instead shows 0 as a value... Shall I understand that still being in its infancy, this concern might be solved in future released?

Excel allows the users to understand the syntax of a formula and gives samples, what are my best shots of understanding the syntax to use for DAX? This is fine but hardly goes in depth...


----------



## cyrilbrd (Jul 16, 2015)

Found in DAX function help the evaluation for CALCULATE, so no more problem there, also solved the percentage issue by simply formatting to %... Thanks again.


----------



## Matt Allington (Jul 16, 2015)

Glad you sorted it. I understand it is hard to get started and hence I wrote a book on the topic. It will be available in late Oct, early Nov this year. You can register here is you want me to notify you when it is available. 

Learn to Write DAX - the book for all Excel users


----------



## cyrilbrd (Jul 16, 2015)

Thanks, registered.  Now I have a concern,  if I filter the table by year the percentage will only work for all years... In short it computes regardless of the filter, is ALL to be changed?


----------



## Matt Allington (Jul 16, 2015)

Yes. So this is where you need to have multiple lookup tables so you can control individual business objects separately. It is not clear from your post if you have this or not. But if you have a calendar table, a location table, a table for "products" or what ever your items actually are, and these are all connected to your transaction table, then you can remove the filter on one or more of these tables leaving the others (eg time period) alone. 

If if you can describe your data a bit more, or post a sample workbook, then I will give you a more precise answer


----------



## cyrilbrd (Jul 16, 2015)

Matt Allington said:


> ...if you can describe your data a bit more, or post a sample workbook, then I will give you a more precise answer



Noted, I will post a sample. Yes I understand the concept but fail to grasp the exact procedure to follow (how to link them, what column is required, should those be unique items...), so far I have only one table. Will get back to you within the next 3 to 4 days.


----------



## Matt Allington (Jul 16, 2015)

So now you are talking about the fundamental principles of Power Pivot and DAX    And yes there is a lot to know and learn.  Once you have it learnt, the world is open to you.  But you do have to learn it.  If you need to do this now, I suggest you read Rob's book.  You can buy the digital edition here DAX Formulas for PowerPivot - Digital Edition -   or from Amazon.


----------



## cyrilbrd (Jul 16, 2015)

Yes, I already ordered that book as well as a few others, also reading the blog (where I discovered you and Avi)... So definitely trying to grasp the PP logic...


----------



## cyrilbrd (Jul 15, 2015)

Given in Query a whole number Column representing volume produced, given another text Column giving locations
I tried to get the percentage of volume per location.
Thought of vol_produced = SUM(table[TOTAL HARVEST])/ALL(table[TOTAL HARVEST]) but this does not work.
Any idea?


----------



## Matt Allington (Jul 16, 2015)

Maybe consider one of Avi's online seminars.  I think these are a good balance between price and "enough" to get started.


----------



## cyrilbrd (Jul 16, 2015)

I would prefer actual seminar or classes rather than webminar,  but will definitely consider your proposal,  and looking forward to read your book.


----------



## cyrilbrd (Jul 17, 2015)

given a table as follows:

Excel 2013ABCDEF1FarmProductionVolumevaluedateyear2127162444184923/1/201520153148864397130533/1/201220124135863227197704/1/201420145146274164182794/1/201520156239043514103183/1/201520157246234161109433/1/201220128240173615143933/1/201320139236993329107652/1/2012201210319351742197953/1/2014201411349414447132124/1/2012201212330392735113104/1/2014201413340693662188891/1/2013201314432792951146244/1/2013201315420491844124174/1/2013201316435043154139991/1/2012201217449644468131803/1/20142014DB


Formula used for percentage of value is :
Percentage = sum('Table'[value])/calculate(sum('Table'[value]),all('Table'))
Field ∑year set as a Slicer
Fields ∑Farm, Production, Volume and Percentage set as Table

I would like the percentage to be filtered by the slicer.


----------



## cyrilbrd (Jul 17, 2015)

Alright, solved with Percentage = sum('Table'[value])/calculate(sum('Table'[value]),allexcept('Table','Table'[year]))
used ALLEXCEPT as it allowed me to use the slicer and returned the correct percentage in the table.
Is this the best method?


----------



## Kvitekvist (Sep 22, 2015)

cyrilbrd said:


> Thanks, registered.  Now I have a concern,  if I filter the table by year the percentage will only work for all years... In short it computes regardless of the filter, is ALL to be changed?



I had a similar challenge and solved it by using "ALLSELECTED" instead of "ALL" in the end:

vol_produced = SUM(table[TOTAL HARVEST])/calculate(sum(table[TOTAL HARVEST]) , allselected(table))

This "ALLSELECTED" will follow any filtering you do as far as I have used it now.

Cheers!


----------

