Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have pivot table with Expression :
% Visibility = sum([Target Visibility Acv])/Count([Customer Key])
% Target = avg([Target Outlet Pct])
Visibility Acv = if(isnull([% Visibility]), Null(), if([% Visibility]>=[% Target],1,0))
Please help so i can get Total Visibility Acv like box in blue (from sum of visibility acv).
I also attach the QVW file.
Really really appreciate if anyone could help me on this.
Thanks a lot
Hi,
try this
If(SecondaryDimensionality() > 0,
if(isnull(sum([Target Visibility Acv])/Count([Customer Key])), Null(),
if(sum([Target Visibility Acv])/Count([Customer Key])>=[Target Outlet Pct],1,0)
),
RangeSum(First(
if(isnull(sum([Target Visibility Acv])/Count([Customer Key])), Null(),
if(sum([Target Visibility Acv])/Count([Customer Key])>=[Target Outlet Pct],1,0)),1,NoOfColumns())))
Regards,
Antonio
Some what i understand your intention but not fully. Here, One option may be Custom Format cell for that?
What was the logic behind the last column?????
Can you explain little bit more???
Regards,
If i understand correctly, there might be some issue with your Total Visibility Acv Expression, because of this issue it's not counting & sum all Visibility Acv values from Different Targets, Hence you're seeing wrong results in Total Visibility Acv.
(Your question is bit confusing: Currently your Total Visibility Acv values are like: 0s, 1s. You are expecting the results like the Yellow colored one, is it correct?)
Hi,
try this
If(SecondaryDimensionality() > 0,
if(isnull(sum([Target Visibility Acv])/Count([Customer Key])), Null(),
if(sum([Target Visibility Acv])/Count([Customer Key])>=[Target Outlet Pct],1,0)
),
RangeSum(First(
if(isnull(sum([Target Visibility Acv])/Count([Customer Key])), Null(),
if(sum([Target Visibility Acv])/Count([Customer Key])>=[Target Outlet Pct],1,0)),1,NoOfColumns())))
Regards,
Antonio
Thanks a lot Antonio. It works