calculate % of subcategory

[illusionek]

Hello

I have a simple PowerPivot table with the following columns/split:

1. Month
2. Store Name
3. Product Category
4. Qty Sold


I am trying to calculate % of tota sale by product category for each store and I keep getting errors. I want to calculate that for example Product 1 was 30% of total sale in Store 1 and perhaps 10% of total sale in Store 2. Unfortunately I end up with calculation as % of the total sale across all stores rather than for each particular.

I tried various combinations of ALL() and ALLSELECTED() and nothing is working.

Any help please?


Many thanks!

 

[theBardd]

If you are just showing Store name and product category you can use

=SUM(Table1[Qty Sold] ) /
CALCULATE( SUM(Table1[Qty Sold] ), ALL( Table1[Product Category] )
)

This will not give what you want if you include Month though, will that be the case, and if so, can you give an example of what you expect?

 

[illusionek]

Many thanks for your prompt response. Unfortunately I need to include Month/Year as well.

Please see below an example of what I am trying to achieve.

Thank you.

Month Year	Store Name	Product No	Sold Qty	% Sold
Jan-15	Store 1	Product 1	5	38%
	Store 1	Product 2	8	62%
	Store 1 Total		13
	Store 2	Product 1	99	64%
	Store 2	Product 2	55	36%
	Store 2 Total		154
Jan-15 Total			167
Feb-15	Store 1	Product 1	44	44%
	Store 1	Product 2	55	56%
	Store 1 Total		99
	Store 2	Product 1	5	4%
	Store 2	Product 2	111	96%
	Store 2 Total		116
Feb-15 Total			215

<tbody>
</tbody>

 

[theBardd]

As you want totals over month, my measure actually returns that result (try it and see).

I might just add one thing as you don't seem to want to want the percentage on sub-total lines,

=IF(HASONEVALUE( Table1[Product Category] ), SUM(Table1[Qty Sold] ) /
CALCULATE(
SUM(Table1[Qty Sold] ), ALL( Table1[Product Category] )
),
BLANK()
)

 

[illusionek]

Many thanks for all your help. I understand now my error. I was providing inorrect argument for ALL. I was declering the whole table without a column.