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
TJCappy
Frequent Visitor

The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

Hi All,

 

Based on the sales for a given period, I am trying to calclate the number of clients a sales rep will need to have to attain their sales goal.

 

I have the following data and can't seem to get the result I need and continuously get the above noted error.

 

I have a table that has the Sales targets for each role in the department

 

Role                                2022 Annual Target              2023 Annual Target

Account Rep - Level I              14,000,000.00                       14,500,000.00     

Account Rep -Level II               19,000,000.00                      20,000,000.00

 

 

I then have a measure that Calculates the avrerage sale per client per rep

 

Distinct Client Gross Sales Average =
DIVIDE (
    'DailySales (cds) Measures'[Total Gross Sales Amount],
    'DailySales (cds) Measures'[Clients with Sales trxns in period],
    "null"
)

 

I am now trying to create a measure that will  divide the target for each rep by the Distinct Client Gross Sales Average to get the a target number of clients.

 

the measure I have created is 

 

Clients Needed for Target =
VAR Target =
    CALCULATE ( 'Targets', 'Targets'[2023 Annual Target] )
VAR AverageSale =
    CALCULATE ( [CNM Distinct Client Gross sales Average] )
RETURN
    Target / AverageSale

 

But it does not work and no matter what I try based on all the info i have come accross on the net I am having no success.

 

I could really do with some help.

 

Thanks

 

 

 

 

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @TJCappy 

please try

Clients Needed for Target =
SUMX (
'Targets',
DIVIDE (
'Targets'[2023 Annual Target],
[CNM Distinct Client Gross sales Average]
)
)

View solution in original post

4 REPLIES 4
TJCappy
Frequent Visitor

Thank you that worked,

If you have the time and patience, would it be possible to explain why. I guess it has something to do with the Table "Targets" but can't figure out the reason for the error.  thanks again.

@TJCappy 

The first argument of CALCULATE has to be sn expression that yields a scalar value but you have specified a table ('Targets').  The second argument of CALCULATE if a Filter expression (in other words a table)

Thank you for explaining, it now makes sense. I am still pretty green with Power BI so getting an explanation really helps.

tamerj1
Super User
Super User

Hi @TJCappy 

please try

Clients Needed for Target =
SUMX (
'Targets',
DIVIDE (
'Targets'[2023 Annual Target],
[CNM Distinct Client Gross sales Average]
)
)

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.

Top Solution Authors