Percentage of Sales

JNM

New Member
Joined
Jan 24, 2012
Messages
35
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.

HTML:
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.
Code:
[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

HTML:
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
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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?
 
Last edited:
Upvote 0

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top