Expression does not work correctly for a few rows

[vidhyamurali]

Hi All,

I have this expression in a pivot. It gives the desired result for all rows, but for one row. I find this weird and I'm not sure how to debug this one.  I appreciate any help with regard to this.

Logic:

row 1 = actual = column with values from table

row 2 = ** to test** = column with expression

the user wants the total of BG-C/MKT-C actuals number to be displayed for both BG-C and MKT-C when the dimension BU is selected.

eg: please see rows 1, 2 and 3

total of BG-C & MKT-C = -0 + 27 = 27

this total is displayed along both BG-C and MKT-C rows and also the sub-total row

Expression:

(sum({<[BG-C/MKT-C]={'BG-C','MKT-C','World'}>}aggr(sum({<[BG-C/MKT-C]={'BG-C','MKT-C','World'}>}[LY Actual MAT - EUR]),[BU],[BG],[Business Model],[Business]))/vUnit)

(this cannot be done in script because we use dynamic dimensions and there are many more dimensions that can be dynamically added apart from BU (shown here) and for each dimension this logic is different and hence the actuals (row 1) value has to be preserved for other logic))

Issue:

for almost all rows the expression works except for a few rows highlighted. I'm not sure how to debug this one. any help is appreciated.

[PrashantSangle]

Hi,

Try with below

Sum(aggr(sum({<[BG-C/MKT-C]={'BG-C','MKT-C','World'}>}[LY Actual MAT - EUR]),[BU],[BG],[Business Model],[Business]))

Regards,

[Anil_Babu_Samineni]

For me your expression is correct. 

(sum({<[BG-C/MKT-C]={'BG-C','MKT-C','World'}>}aggr(sum({<[BG-C/MKT-C]={'BG-C','MKT-C','World'}>}[LY Actual MAT - EUR]),[BU],[BG],[Business Model],[Business]))/vUnit)

But, Need to understand why outer set expression required? May be use NODISTINCT 

(sum({<[BG-C/MKT-C]={'BG-C','MKT-C','World'}>}aggr(NODISTINCT sum({<[BG-C/MKT-C]={'BG-C','MKT-C','World'}>}[LY Actual MAT - EUR]),[BU],[BG],[Business Model],[Business]))/vUnit)

[sunny_talwar]

I am curious to know what is

vUnit

Is this 0 for some reason on the rows highlighted? What do you see when you use your expression without vUnit

(Sum({<[BG-C/MKT-C] = {'BG-C','MKT-C','World'}>} Aggr(Sum({<[BG-C/MKT-C] = {'BG-C','MKT-C','World'}>} [LY Actual MAT - EUR]), [BU], [BG], [Business Model], [Business])))