DAX Calculate function

[M1donne]

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

 

[scottsen]

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 ??

 

[M1donne]

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.

 

[scottsen]

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?