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]

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

 

[cyrilbrd]

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

 

[cyrilbrd]

given a table as follows:

Excel 2013
	A	B	C	D	E	F
1	Farm	Production	Volume	value	date	year
2	1	2716	2444	18492	3/1/2015	2015
3	1	4886	4397	13053	3/1/2012	2012
4	1	3586	3227	19770	4/1/2014	2014
5	1	4627	4164	18279	4/1/2015	2015
6	2	3904	3514	10318	3/1/2015	2015
7	2	4623	4161	10943	3/1/2012	2012
8	2	4017	3615	14393	3/1/2013	2013
9	2	3699	3329	10765	2/1/2012	2012
10	3	1935	1742	19795	3/1/2014	2014
11	3	4941	4447	13212	4/1/2012	2012
12	3	3039	2735	11310	4/1/2014	2014
13	3	4069	3662	18889	1/1/2013	2013
14	4	3279	2951	14624	4/1/2013	2013
15	4	2049	1844	12417	4/1/2013	2013
16	4	3504	3154	13999	1/1/2012	2012
17	4	4964	4468	13180	3/1/2014	2014
DB

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]

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]

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!