How calculate percent values??

[Jalca]

I execute a query that returns something like : Sales per State (and per cities inside them).(I think) Toppercent is not the best function because I lost information, any idea to show "cities % values" respect its state ( it should show 100)

 

[Kelee]

Not quite sure what you mean, but try: Length measure/sum(Members in filter for current dimension,Length measure)*100

 

[Jalca]

Hi, I try again: my "geographical" dimension contains State, Zone, Area, City, Bricks.
My query returns "Sales values" on columns and State>Area>Bricks on rows (not entire structure). I'm trying to show the % values for each level in relation with its previous (bricks-->Area, Area-->State).
I hope it is more clear! Thanks in advance

 

[P1Daniels]

Look up "calculated measure" in your Help menu...

 

[Kelee]

Create a Calculated Measure as follows:
[Unit Sales]/SUM({[Product].Parent}, [Unit Sales])
then in advanced properties, set your format string to #.00%

(It was the curley brackets that got me)
You may find that you come out to 99% because of the rounding and you may get something strange at the (All) level - use iif to set it to always display 100% at the (All) level
Hope this helps

 

[Jalca]

Hi again!!
First of all thanks for all helps.

I am going to expose my question and the solution I have found, I hope it will be uselfuness.

I have a "geography" dimension and a "multilevel" report like:

StateB 182400 ERROR!!!
regionc 120000 100%
city1 24000 20%
city8 60000 50%
regiond 24000 100%
city1 2400 10%
city2 9600 40%
city3 12000 50%

The objective was to show "city % values respect region" and also "region % values State", I just have got the first one with next query:
WITH

SET [LastLevel] AS '{[city1],[city2],..,[cityn]}'

// extract just Region parent of lastlevel.members
SET [RegionParent] AS ' GENERATE ([LastLevel],
{ANCESTOR ( [Geo].CURRENTMEMBER,[Geo].[region])})'
// The same with State
Set [StateParent] AS ' GENERATE ( [LastLevel],{ANCESTOR ( [Geo].CURRENTMEMBER,[Geo].[State])})'

Set [OrderLastInRegion] AS ' HIERARCHIZE ( { [LastLevel] , [RegionParent]})'

Set [RegionInState] AS ' HIERARCHIZE ([OrderLastInRegion] , [StateParent]})'

//create a calculated mesure respect Region Level
MEMBER [Measures].[% Sales respect Region] AS ' [Measures].[Sales] /( [Measures].[Sales], ANCESTOR ([Geo] ,[Geo].[region] ))', FORMAT_STRING = '#.00%'

SELECT {[Measures].[% Sales respect Region]} ON COLUMNS,
{[RegionInState]} ON ROWS
FROM

Thanks for all.
Jalca

 

[Kelee]

Here are another 2 MDX statements that work:

iif(Product.CurrentMember.Level.Ordinal > 0, ((Product.CurrentMember,
[Store Sales]) / (Product.CurrentMember.Parent, [Store Sales])),
((Product.CurrentMember, [Store Sales]) / (Product.CurrentMember, [Store Sales])))

or:
iif(Product.CurrentMember.Level.Ordinal > 0,
(([Product].CURRENTMEMBER, Measures.[Unit Sales]) /
([Product].CURRENTMEMBER.PARENT, Measures.[Unit Sales])),1)