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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.