Commission table DAX calculation

DAX DAX Calculations
1

Hi all,

image
I had use DISTINCOUNT to get the total new customer for the sales person.

image
I had another table to store the commission rate by period and the condition.

image
The result as above.

  1. for those new customers equal to or more than 100, commission rate would be 25
  2. for those new customers between 1 to 99, commission rate would be 20
  3. the date would be between 1-6-20 to 31-8-20

Kindly assist.

Thank you.

Regards,
Dennis Tiong

2

Hello @dennistgc,

Thank You for posting your query onto the Forum.

Is it possible for you to upload the working of your file?

Thanks and Warm Regards,
Harsh

3

Hi Harsh,

Not able to upload whole working file as the file is large. Basically the question is small part of the whole working file. I had a fact table which have linked with date table. Then I use DISTINCOUNT to calculate the quantity of the new customers.

Thank you.

Dennis Tiong

4

Hello @dennistgc,

Actually the thing is first 2 conditions can be easily done using the “SWITCH( TRUE() )” function but for the last condition it’s bit tricky to write a formula without accessing the data model. Below is the screenshot provided about how to satisfy the first 2 conditions -

Commission Table

Thanks and Warm Regards,
Harsh

5

Hi @Harsh,

Actually what I am trying to do is put the criteria in a table with a date range which is indicated as above.

Thank you.

Dennis

6

image

The above is the data model. Kindly ignore the rest of the relationship. NParentID is used to count those new customers.

Thank you.

7

Thanks. I had found the solution.