Quadruple Nested SUMX or CROSSJOIN
I had an interesting problem with a client last week where I built a data model to calculate rebates payable to customers based on their sales turnover. There were a few challenges that I am going to cover below, including:
- There needs to be a user friendly way to capture the rebate data, but it then needs to be transformed so it can be used in the model.
- There is a nested SUMX problem – quadruple nesting in this case. I solve this with a CROSSJOIN (and then SUMMARIZE) that I will explain below.
- There is also a quadruple nested IF(HASONEVALUE()) problem that I solve with an innovative formula that I will also cover below.