Report Portal

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:

  1. 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.
  2. 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.
  3. There is also a quadruple nested IF(HASONEVALUE()) problem that I solve with an innovative formula that I will also cover below.

Read more...

 

Tags: dax, tabular

 

2007-2015 VidasSoft Systems Inc.