New Measure Issue - 'single value for column' error

[Scussett]

Hi All,

I'm new to PBI.  I'll start at the beggining!  I'm trying to create a Sales Leaderboard with data coming from our CRM.  I have the following relationships:

 The reason I'm putting together the relationships this way is for a couple of reasons:

1. Order Line Item and Order Split have no direct relationship.  So one Order can have many line items, and many splits.
2. The sales personnel I want to track are not always the owner of the order (hence why there is a relationship from Order Split to User).

Here is what I want to be able to do:

1. At the Order Line Item level, I only want to pay sales people for the first 12 months of a deal.  I have the start and end date of each Order Line Item, and I created a column called 'Pro Rated Margin' that looks at the Order Line Item and determines the amount for the first 12 months. This works.
2. At the Order level, I have created a column called Pro Rated Margin Total that sums the Order Line Item's Pro Rated Margin for the Order ID.  This works.
3. At the Order Split level, I have a measure (Split Margin Total) that sums the Order's Pro Rated Margin Total for that Order, and I want to multiply it through by the Split Percentage/100.  This does not work!
   1. It works when I get the sum of Pro Rated Margin Total for that Order ID - but I cannot seem to get it to work when I multiply thourgh by Split Percentage/100.  This causes the 'A single value for column...' error.
   2. This is the function I am using:
      1. Pro Rated Margin - Order Total = SUMX('Order (2)',
         CALCULATE(VALUES('Order (2)'[Pro Rated Margin Total]),FILTER('Order Split','Order Split'[Order__c] = 'Order (2)'[Id])
         )
         )

Here is a practical example:

1. Order A has two Order Line Items:
   1. Software 1, which is a 24 month license for 24,000 USD.
   2. Software 2, which is a 12 month license for 10,000 USD.
2. Order A has two Order Splits:
   1. Person 1 has a 50% split.
   2. Person 2 has a 50% split.
3. Order B has one Order Line Item:
   1. Software 1, which is a 12 month license for 5,000 USD.
4. Order B has one Order Split
   1. Person 1 has a 100% split.

For Order A, the Pro Rated Margin Total equals the sum of the Pro Rated Margin of each Order Line Item:

• This is calculated as (12/24 * 24,000) + (12/12 * 10,000) = 22,000.

For Order A, the Order Split's Split Margin Total for Person 1 is 11,000, and for Person 2 it is 11,000.

For Order B, the Pro Rated Margin Total is: (12/12 * 5,000) = 5,000.

For Order B, the Order Split's Split Margin Total for Person 1 is 5,000.

The Sales Leaderboard would then be calculated as:

1. Person 1 - 17,000
2. Person 2 - 11,000

I'm happy to upload some data, I just need to make it sample data.  Should I just create a new set of basic tables to handle this?

I appreciate all of your help.

Thanks!

[parry2k]

Hey @Scussett

If you can put sample data in excel sheet as different tabs and send it over, I will put something for you.

Sending you my email address thru private message. Cheers!

[parry2k · Accepted solution]

Hey @Scussett

I sent you the solution (pbix) file by mail, hope it worked out. Cheers!

[Anonymous]

I knows its been a while, but are you able to send a copy of this to me as well? This is exactly what I'm looking for.

[Scussett]

What you sent me worked like a charm.  Just wanted to say thank you!