Hi,
I am having difficulties using nested LODs in my workbook. I have four dimensions: Customer ID, PO Number, Alternative, Line Item. I want to calculate the highest Alternative for each PO Number and then SUM these highest Alternatives at higher levels.
Here's my process:
Step 1 - I don't have data at Line Item granularity, so the amount is repeated for all Line Item rows. So, I exclude Line Item entirely and calculate amount for each Alternative as a MAX of all Line item amounts.
Step 2 - For each Customer ID & PO Number, I take a MAX of all Alternatives.
Step 3 - At Customer ID level, SUM all highest Alternatives from each PO Number - this is where I fail, because although my total is correct (714k), I lose the amounts on individual Alternatives (circled red).
I wonder if I am missing something obvious? I would be grateful for some guidelines.
Thank you.