Forum Discussion

RoxyGCT's avatar
RoxyGCT
Regular Visitor
6 years ago

Productx Incorrect result using a measure

Hi there

 

I need to calculate a rolling return on a weighted average return (calculated in a measure). This is in theory 'working' but the results are a bit off, almost like the rounding isnt correct. 

 

I have applied the same formula to a column (Measure 3 below) and to the measure (with the same values - Measure 2). The productx of the the column is correct, the same formaula on the measure is not correct.

 

Measure 2 and 3 SHOULD be the same?

 

Measure 3 = CALCULATE(PRODUCTX(Performance1,Performance1[performance]),DATESINPERIOD(Performance1[dat],LASTDATE(Performance1[dat]),-(Allselected(NumberTable[Number],NumberTable[Number])),MONTH))
 
Measure 2 = CALCULATE(PRODUCTX(AllMeasures,[1 Perf W1]),DATESINPERIOD(Performance1[dat],LASTDATE(Performance1[dat]),-(Allselected(NumberTable[Number],NumberTable[Number])),MONTH))
 
MEASURE:
1 Perf W1 = (if(IF(ISFILTERED(Performance1[nam]),DISTINCTCOUNT(Performance1[nam]),IF(ISBLANK(SELECTEDVALUE(Performance1[nam])),0))= 1,SUM('Performance1'[Orig Performance]),0) * SUM('%F1W1'[Weight])+if(IF(ISFILTERED(Performance2[nam]),DISTINCTCOUNT(Performance2[nam]),IF(ISBLANK(SELECTEDVALUE(Performance2[nam])),0))= 1,SUM('Performance2'[Orig Performance]),0) * SUM('%F2W1'[Weight])+if(IF(ISFILTERED(Performance3[nam]),DISTINCTCOUNT(Performance3[nam]),IF(ISBLANK(SELECTEDVALUE(Performance3[nam])),0))= 1,SUM('Performance3'[Orig Performance]),0) * SUM('%F3W1'[Weight])+if(IF(ISFILTERED(Performance4[nam]),DISTINCTCOUNT(Performance4[nam]),IF(ISBLANK(SELECTEDVALUE(Performance4[nam])),0))= 1,SUM('Performance4'[Orig Performance]),0) * SUM('%F4W1'[Weight])+if(IF(ISFILTERED(Performance5[nam]),DISTINCTCOUNT(Performance5[nam]),IF(ISBLANK(SELECTEDVALUE(Performance5[nam])),0))= 1,SUM('Performance5'[Orig Performance]),0) * SUM('%F5W1'[Weight]))+1
 
COLUMN:
 
performance = Performance1[Orig Performance]+1
 
 

 

 

2 Replies