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)
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
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
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])})'
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.