Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello, I have been asked to update an existing Pivot table to show an aggregation percent at each level. I have attached an example of what the table currently looks like, and what the desired view is as well.
Current calculation for % of Sales:
Sum(Aggr(Sales{<Date = {">=$(=Date(MonthStart(Today())))<=$(=Date(Today()))"}>}Sales),[VP Name],Branch))
/
Sum(Total{<Date = {">=$(=Date(MonthStart(Today())))<=$(=Date(Today()))"}>}Sales)
I am looking to get the aggregation of the branches to sum up at a VP level. So VP S has 15.9% of total sales and branch 30 has 3.9% of Total Sales. But I would like to have this Pivot Table show Branch 30 as 24.4% of sales for VP S (103,692.63/424,618.66) instead.
Is it possible to have both aggregation layers show without having to select a VP Name to adjust the 'Total' sales amount?
Hi
not sure to understand your need but it seems who want the % of VP Name sales on TOTAL Sales and % of Branch sales on total sales PER VP Name in the same mesure.
Am I Wright ? if so you can use dimentionnality () to do that with an if statement
here a simple exemple :
if(Dimensionality()=1,
sum(Volume)
/
sum(total Volume), // it will divide sum sales per TOTAL sum Sales then
sum(Volume)
/
sum(total <DIM2> Volume) // it wil divide sales per Sub Total per DIM2 ( branch for you )
)
hopt it helps
Hi
not sure to understand your need but it seems who want the % of VP Name sales on TOTAL Sales and % of Branch sales on total sales PER VP Name in the same mesure.
Am I Wright ? if so you can use dimentionnality () to do that with an if statement
here a simple exemple :
if(Dimensionality()=1,
sum(Volume)
/
sum(total Volume), // it will divide sum sales per TOTAL sum Sales then
sum(Volume)
/
sum(total <DIM2> Volume) // it wil divide sales per Sub Total per DIM2 ( branch for you )
)
hopt it helps
Thank you, @brunobertels ! This helps solve my initial problem. I am able to assign dimensionality based on the order of dimensions in the Pivot table.
My new issue is that if the order of the dimensions is changed then the calculation errors. Is there a way to assign dimensions based on order, similar to Column(1), Column(2) for measures?
Ex: Dimension Order: VP_Name, Branch, Product
Reordered Dimension: VP_Name, Product, Branch
Please let me know if this question makes sense?
Hi
it makes sense.
You will be able to rearrange your mesure depending of the order of the dimension using getObjectDimension() function
this function give the name of the dimension according to his index
So it will be someting like this
if(GetObjectDimension(Dimensionality()-1) ='Branch', YourMesureForBranchHere , YourMesureForProductHere)
this :
GetObjectDimension(Dimensionality()-1) will give you the Name of the second dimention in your PivotTable