Indexing two powerpivot colums - both expresed as '% of clumn total'

[M1donne]

Hi all

I have asked this before but still unable to get a solution...

I have a Powerpivot Dashboard reporting two columns, sales and stock by Vendor with both sales and stock expressed as '% of column total' in Value Field Settings

I'm tying to create a calculation to divide the sales % by the stock % to give an INDEX (where >1 is positive and <1 is negative)

Since the pivot table reports both the sales column totaling 100% and the stock column totaling 100% I assumed it would be as simple as putting in a calculated field but the option is greyed out in POWERPIVOT TOOLS - OPTIONS - FIELDS, ITEMS AND SETS

Does anyone know a calculation to achieve this (DAX?)

I did wonder whether I could create a calculated field in the main DATA body but I don't think that would be dynamic whereas the Pivottable is.

Any assistance would be gratefully appreciated.

Regards

 

[scottsen]

Ya, fancy formatting tricks are just NOT going to help you. You have to do this in straight dax, and it's a bit of a pain since there is no way to generically write a "percent of parent" measure. Depending on how many flavors of pivots, you end up with "% of Category", "% of Sub-Category", "% of Vendor", etc.

If you have a "real" percentage measure...

MyPercent := DIVIDE( SUM(MyTable[MyColumn]) , CALCULATE( SUM(MyTable[MyColumn]) , ALL(MyTable[MyColumn]) ) )

Then you can certainly divide 2 measures together...