Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Scussett
Frequent Visitor

New Measure Issue - 'single value for column' error

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:Sales Leaderboard Right Side Relationships.png

 

 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!

 

 

1 ACCEPTED SOLUTION

Hey @Scussett

 

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

4 REPLIES 4
parry2k
Super User
Super User

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!



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Hey @Scussett

 

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.