Percentage computation in Power BI Designer

[cyrilbrd]

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]

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]

...
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]

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]

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]

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]

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]

...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]

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]

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...