Want to merge transaction line in SAC model based on a dimension member

[saikhancs35]

Hello,

I am using SAC planning model and have data like this:

DIM A	DIM B	DIM C	Amount
01/04/2025	APEQ	MP1	55
01/04/2025	APEQ	MP2	55
01/04/2025	APEQ	MP3	55
02/04/2025	APEQ	MP1	70
02/04/2025	APEQ	MP2	70
02/04/2025	APEQ	MP3	70

I want data to be like this:

DIM A	DIM B	DIM C	Amount
01/04/2025	APEQ	MP	55
02/04/2025	APEQ	MP	70

So basically, i want to merge my DIM C lines to 1 line and put the average of the amount against it. And later want to delete those 3 lines.

Please not that i already have master data updated for DIM C.

Thanks,

[William_Yu]

I believe you can achieve this with advance formula if you maintain target DIM C as property of existing DIM C. 

[saikhancs35]

Can you please elaborate, how we can achieve this? Lets say ..I have attribute in DIM C which having value as MP for dimension member MP1,MP2,MP3

[William_Yu]

1. collect all value to new DIM C value MP from original value same measure,
2. as part of step 1, collect a count with virtual measure member. 
3. devide total value by count on MP 
4. delete value on MP1, MP2, MP3. 

[saikhancs35]

can you provide a script for this?

[William_Yu]

I would suggest you first try on your own with advance formula by referencing some examples from SAC online help, you can find the exact example for step 1 from here: https://help.sap.com/docs/SAP_ANALYTICS_CLOUD/00f68c2e08b941f081002fd3691d86a7/e88b9a87633c4e19a0dbd...