DAX Calculate function

M1donne

New Member
Hi all

I have a table of data, effectively sales and stock by week (several weeks in the table), I'm trying to create a calculation to show sales mix in one column and stock mix in another, with the ability to rollup the sales by category , I would normally use the following calculation..

=sales/(sumif(sales (where week=x)) which would give me a % for a particular week which I could then do the same for stock column to get the stock mix.

However I have tried to perform the calculation in my powerpivot table without success and wondered if the DAX CALCULATE Function would achieve the same results.

Anyone know if this is possible?

Regards

M1donne

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
The short answer is : Yes, it can be done

I'm a little unclear on what you want to achieve. The % is... the % of total sales that THIS week represents out of the whole (year?) ? or ??

The short answer is : Yes, it can be done

I'm a little unclear on what you want to achieve. The % is... the % of total sales that THIS week represents out of the whole (year?) ? or ??

Hi there

thanks for the response.

The end result I'm trying to achieve is to get a %mix of sales (by whatever i choose to slice it by, ie category, region or supplier etc) for a particular week or set of weeks, then compare it to a % mix of stock for the same week(s).

For instance category A sales mix of 40% and stock mix of 20% (as an example) would show this category as over performing with a positive index (40%/20%).

i hope this is clearer.

Okay, I gotcha. I think

So... Let's say Categories are on rows of your pivot.

Health & Beauty \$200
Alcohol \$400
Snacks \$300

Total \$800. So, Alcohol is 50% of your sales (by category).

Total Sales := SUM(SalesTable[Sales])
ALL Category Sales := CALCULATE([Total Sales], ALL(Categories))
% Category Sales := DIVIDE([Total Sales], [ALL Category Sales])

You can then do similar for stock and divide those two %measures just fine.

Is that what we are going for?

Replies
0
Views
60
Replies
6
Views
487
Replies
1
Views
291
Replies
0
Views
883
Replies
1
Views
83

1,207,436
Messages
6,078,546
Members
446,346
Latest member
shinbum

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.

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

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