cancel
Showing results 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

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
Super User

Hi @TJCappy

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

4 REPLIES 4
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.

Super User

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)

Frequent Visitor

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

Super User

Hi @TJCappy

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