Measure as a Dimension in Pivot Table

[ITMercon]

Hey everyone! I'm trying to do a pivot table using the result of one measure as the columns of the Pivot Table. 

Measure>: IF(($(Acum.CY) + $(Acum.LY) - Sum({<type={'1'}>} distinct Amount))>=41,'Muy Critico',
IF(($(Acum.CY) + $(Acum.LY) - Sum({<type={'1'}>} distinct Amount))>=31,'Critico',
IF(($(Acum.CY) + $(Acum.LY) - Sum({<type={'1'}>} distinct Amount))>=16,'Excede Rango',
IF(($(Acum.CY) + $(Acum.LY) - Sum({<type={'1'}>} distinct Amount))>=0,'Dentro del rango',
IF(($(Acum.CY) + $(Acum.LY) - Sum({<type={'1'}>} distinct Amount))<0,'Saldo Negativo',''
)))))

I made it as a measure because the buckets are the result of 2 more measures that I stored as Variables  AcumCY (Accumulated Current Year) and Acum.LY (Accumulated Current Year). I have managed to add the buckets in the pivot table using the Valuelist function and I got this, however, the numbers under each column are not correct. 

I created the expression IF(($(Acum.CY) + $(Acum.LY) - Sum({<type={'1'}>} distinct Amount)) as a variable (vFinalBalance) to incorporate it in the measure I formulated for this Table: IF( Valuelist('Muy Critico','Critico','Excede el Rango','Dentro del Rango','Saldo Negativo')= 'Muy Critico',
Count((vFinalBalance > 41)),
IF( Valuelist('Muy Critico','Critico','Excede el Rango','Dentro del Rango','Saldo Negativo')= 'Critico',
Count(distinct (if(vFinalBalance >= 31, KeyEmployee))),
IF( Valuelist('Muy Critico','Critico','Excede el Rango','Dentro del Rango','Saldo Negativo')= 'Excede el Rango',
Count(distinct (if(vFinalBalance >= 16, KeyEmployee))),
IF( Valuelist('Muy Critico','Critico','Excede el Rango','Dentro del Rango','Saldo Negativo')= 'Dentro del Rango',
Count(distinct (if(vFinalBalance >= 0, KeyEmployee))),
IF( Valuelist('Muy Critico','Critico','Excede el Rango','Dentro del Rango','Saldo Negativo')= 'Saldo Negativo',
Count(distinct (if(vFinalBalance <0, KeyEmployee))),
)))))

Can anyone guide me on this? I want to allocate per Organizational Unit the number of employees who have a Final Balance in each specified range: 'Muy Critico','Critico','Excede el Rango','Dentro del Rango','Saldo Negativo'.

Thanks in advance

[Ksrinivasan]

hi,

some Bracket are missing to calculate, you can try

 IF(((($(Acum.CY) + $(Acum.LY)) - (Sum({<type={'1'}>} distinct Amount)))>='41','Muy Critico','')

and also confirm whether you want to Count of distinct Amount or Sum of distinct Amount,

ksrinivasan.

[ITMercon]

Hey!! thanks a lot for the input, I completed the missing brackets and I fixed the distinct to Amount and it worked properly in one of my columns !! However it's not showing any results for the other columns.

IF( Valuelist('Muy Critico','Critico','Excede el Rango','Dentro del Rango','Saldo Negativo')= 'Muy Critico',
Count( aggr( (if($(vFinalBalance) >= '41' ,Amount)),Amount,KeyEmployee,[Business Unit])),
IF( Valuelist('Muy Critico','Critico','Excede el Rango','Dentro del Rango','Saldo Negativo')= 'Critico',
count( aggr( (if($(vFinalBalance) >= '31',Amount)),Amount,KeyEmployee,[Business Unit])),
IF( Valuelist('Muy Critico','Critico','Excede el Rango','Dentro del Rango','Saldo Negativo')= 'Excede el Rango',
count( aggr(distinct (if($(vFinalBalance) >= '16',Amount)),Amount,KeyEmployee,[Business Unit])),
IF( Valuelist('Muy Critico','Critico','Excede el Rango','Dentro del Rango','Saldo Negativo')= 'Dentro del Rango',
count( aggr(distinct (if($(vFinalBalance) >= '0',Amount)),Amount,KeyEmployee,[Business Unit])),
IF( Valuelist('Muy Critico','Critico','Excede el Rango','Dentro del Rango','Saldo Negativo')= 'Saldo Negativo',
count( aggr(distinct (if($(vFinalBalance) <'0',Amount)),Amount,KeyEmployee,[Business Unit])),
)))))