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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors