Skip to Content
0
Apr 21, 2023 at 07:15 AM

BPC Member Dimension Formula - Issue with the YTD measures

241 Views

Dear SAP Communty,

We are having an issue with the calculation of the member formula: we have created several member formulas on the account dimension that calculate a ratio type (A/B) at the lowest level of the entity dimension. At the Entity Dimension node level, we want to sum all lowest members of the Entity Dimension. In other words, the objective is to have the sum of the ratios of the lowest level but at the parent level.So we use this MDX formula: ETP_SST1 = IIF([NBJO]=0,NULL,IIF([ENTITY].CURRENTMEMBER.PROPERTIES("CALC")="N", ([D12])/IIF([NBJO] = 0,NULL,[NBJO]),SUM(DESCENDANTS([ENTITY].CURRENT MEMBER,20,LEAVES),(([D12])/IIF([NBJO]=0,NULL,[NBJO])))))
This formula works well at every level when we have data for a month, both in periodic and YTD measures. But this formula doesn't work in YTD when we don't have data for a month and we don't understand why, because source accounts are not empty in YTD measure.
In order to be sure that there is no problem with a division by zero, I simplified the formula using only the sum of one account: SUM(DESCENDANTS([ENTITY].CURRENTMEMBER,99, LEAVES ),[DECOMPO_2]). Unfortunately, the behavior is the same.
Below is an example with two cases of entities: one with data and the other without data :

image.png

Attachments

image.png (27.1 kB)