Percentage of Sales

[JNM]

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]

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]

Awesome ! Changing to Show Total as did the job!