cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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]
)
)

Announcements

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Power BI Monthly Update - May 2024

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

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors