Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Friends,
I have one pivot table in which 5 dimensions and i am showing total , but total is coming zero.
Any idea guys .
Thanks
What is your expression? Is it an if statement? May be you need to use Sum(Aggr()) around your expression
here is the expression
(if(Dimensionality()>=3 and (Only(Priorities)='Elevate the Core' or GetFieldSelections(Priorities)='Elevate the Core'),
((sum({$<_PFlag={'ORG'},FINANCIAL_ACCOUNT_KEY={'$(vKPI1AccountKey)'},VARIANCE_BASE_SK={$(vVariance)},ACTUALITY_SK={'AC'}>}VOL_PREV_AMENDED))/1000000+$(vETC_LY_AC_VPA41_ZB1)/1000000),
if(Dimensionality()>=3 and (Only(Priorities)='NABLAB' or GetFieldSelections(Priorities)='NABLAB'),
((sum({$<_PFlag={'ORG'},FINANCIAL_ACCOUNT_KEY={'$(vKPI1AccountKey)'},VARIANCE_BASE_SK={$(vVariance)},ACTUALITY_SK={'AC'}>}VOL_PREV_AMENDED))/1000000+$(vNAB_LY_AC_VPA59_ZB1)/1000000),
if(Dimensionality()>=1 and (Only(Priorities)='Global' or GetFieldSelections(Priorities)='Global'),
sum({$<_PFlag={'EXT'},FINANCIAL_ACCOUNT_KEY={'$(vKPI1AccountKey)'},VARIANCE_BASE_SK={$(vVariance)},ACTUALITY_SK={'AC'}>}VOL_PREV_AMENDED)/1000000,
sum({$<_PFlag={'ORG'},FINANCIAL_ACCOUNT_KEY={'$(vKPI1AccountKey)'},VARIANCE_BASE_SK={$(vVariance)},ACTUALITY_SK={'AC'}>}VOL_PREV_AMENDED)/1000000
))))
Not sure what your dimensions are, but try like this (assuming you have two dimensions; Dim1 and Dim2)
Sum(Aggr(
(if(Dimensionality()>=3 and (Only(Priorities)='Elevate the Core' or GetFieldSelections(Priorities)='Elevate the Core'),
((sum({$<_PFlag={'ORG'},FINANCIAL_ACCOUNT_KEY={'$(vKPI1AccountKey)'},VARIANCE_BASE_SK={$(vVariance)},ACTUALITY_SK={'AC'}>}VOL_PREV_AMENDED))/1000000+$(vETC_LY_AC_VPA41_ZB1)/1000000),
if(Dimensionality()>=3 and (Only(Priorities)='NABLAB' or GetFieldSelections(Priorities)='NABLAB'),
((sum({$<_PFlag={'ORG'},FINANCIAL_ACCOUNT_KEY={'$(vKPI1AccountKey)'},VARIANCE_BASE_SK={$(vVariance)},ACTUALITY_SK={'AC'}>}VOL_PREV_AMENDED))/1000000+$(vNAB_LY_AC_VPA59_ZB1)/1000000),
if(Dimensionality()>=1 and (Only(Priorities)='Global' or GetFieldSelections(Priorities)='Global'),
sum({$<_PFlag={'EXT'},FINANCIAL_ACCOUNT_KEY={'$(vKPI1AccountKey)'},VARIANCE_BASE_SK={$(vVariance)},ACTUALITY_SK={'AC'}>}VOL_PREV_AMENDED)/1000000,
sum({$<_PFlag={'ORG'},FINANCIAL_ACCOUNT_KEY={'$(vKPI1AccountKey)'},VARIANCE_BASE_SK={$(vVariance)},ACTUALITY_SK={'AC'}>}VOL_PREV_AMENDED)/1000000
))))
, Dim1, Dim2))
I have 5 dimensions , but on 3rd dimension i need to do some other calculation for particular value , rest should work as its .
so should i add all 5 dimension in your above expression?
Not entirely sure you are doing, but may be this:
If(Dimensionality() >= 3 and (Only(Priorities) = 'Elevate the Core' or GetFieldSelections(Priorities) = 'Elevate the Core'),
Sum({$<_PFlag = {'ORG'}, FINANCIAL_ACCOUNT_KEY = {'$(vKPI1AccountKey)'}, VARIANCE_BASE_SK = {$(vVariance)}, ACTUALITY_SK = {'AC'}>} VOL_PREV_AMENDED)/1000000 + $(vETC_LY_AC_VPA41_ZB1)/1000000,
If(Dimensionality() = 3 and (Only(Priorities)='NABLAB' or GetFieldSelections(Priorities)='NABLAB'),
Sum({$<_PFlag = {'ORG'}, FINANCIAL_ACCOUNT_KEY = {'$(vKPI1AccountKey)'}, VARIANCE_BASE_SK = {$(vVariance)}, ACTUALITY_SK = {'AC'}>} VOL_PREV_AMENDED)/1000000 + $(vNAB_LY_AC_VPA59_ZB1)/1000000,
If(Dimensionality() >= 1 and (Only(Priorities)='Global' or GetFieldSelections(Priorities)='Global'),
Sum({$<_PFlag = {'EXT'}, FINANCIAL_ACCOUNT_KEY = {'$(vKPI1AccountKey)'}, VARIANCE_BASE_SK = {$(vVariance)}, ACTUALITY_SK = {'AC'}>} VOL_PREV_AMENDED)/1000000,
Avg(Aggr(Sum({$<_PFlag={'ORG'}, FINANCIAL_ACCOUNT_KEY = {'$(vKPI1AccountKey)'}, VARIANCE_BASE_SK = {$(vVariance)}, ACTUALITY_SK={'AC'}>} VOL_PREV_AMENDED)/1000000, Dim1, Dim2, Dim3, Dim4, Dim5))
)))