# Percentage of Sales



## JNM (Jan 24, 2012)

Good afternoon,
I'm a novice user of PowerPivot, but have used Pivot tables many times. I'm having trouble creating a DAX formula that shows the percent of sales for a list of items that will respond to a row filter (item filter). I get the percentage of total sales for all items, but when I filter on one group within the filter the line amounts don't change.


```
Filter (ALL)
Item   Pur   Inv   Sold   % of Sales
1       168    24     145    .013297
2         50    4       46     .004218
3        29     3       25     .002293
4       217    26     183      .016781
 
Filter 1
Item   Pur   Inv   Sold   % of Sales
1       168    24     145    .013297
```
 
Here's a copy of the formula in the % of Sales Measure.

```
[SIZE=1]=(sum('MyTable'[SalesQuantity]))/Calculate(Sum('MyTable'[SalesQuantity]),ALL('MySalesTable'))[/SIZE]
```
 
How should it be rewritten to allow the filter to work and recalculate on the remaining rows? So it works like the following


```
Filter (ALL)
Item   Pur   Inv   Sold   % of Sales
1       168    24     145    .013297
2         50    4       46     .004218
3        29     3       25     .002293
4       217    26     183      .016781
 
Filter 1
Item   Pur   Inv   Sold   % of Sales
1       168    24     145    100.00
```
 
Thank you.

JNM


----------



## powerpivotpro (Jan 24, 2012)

PowerPivot V2 is introducing a new function called ALLSELECTED() which will address this exact need - you will use ALL() when you want "% of _all_ products" and ALLSELECTED() when you want "% of all _selected _(visible) products."

In the meantime, in v1, I am not aware of a way to do this.  I will noodle on it a bit though as it seems like something I could hack together.

EDIT:  all right it's out of my league in v1, or at least not something I can come up with quickly.  Can you hack it for now using "Show Totals As" in the pivot itself?


----------



## JNM (Jan 24, 2012)

Awesome ! Changing to Show Total as did the job!


----------

