cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors