cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PaulMac Member
Member

Having Issues With a Simple Calculation

Hello all

 

I am trying to replicate an excel formula in Power BI but I can't seem to do it.

 

I am trying to calculate the Total Calls per 1000 files uploaded.

 

In Excel column B contains Number of Calls Offered

Column C contains Number of Files Uploaded

 

My Excel formula reads as:

 

=B2/C2*1000

 

This get me the result I need in excel

 

I have tried to write this out in Power BI as:

 

Calls per 1000 API Files Uploaded = 
CALCULATE('All_Phones'[Offered]/'Files Uploaded'[API])*1000

 

But instead of a result I get the error message below

 

"A single value for column 'Offered' in table 'All_Phones' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

 

Please let me know where I am going wrong so I can stop feeling like an invalid sod!

 

Many thanks in advance for any help.

 

Regards

Paul

9 REPLIES 9
_donev Frequent Visitor
Frequent Visitor

Re: Having Issues With a Simple Calculation

I assume your data looks like the picture below: 

1.png

 

In that case, if you try to create a New Column, your formula will work properly (as in the image above), as long as you are not using CALCULATE. 

PaulMac Member
Member

Re: Having Issues With a Simple Calculation

Hi @_donev

 

No, my data looks like the picture below:

 

Capture.PNG

 

Any thoughts?

 

Kind regards

Paul

 

Highlighted
_donev Frequent Visitor
Frequent Visitor

Re: Having Issues With a Simple Calculation

@PaulMac,
Can you share a snap of your model? Are the 'All_Phones'[Offered] and 'Files uploaded'[API] measures? Are you creating a new measure or column?
PaulMac Member
Member

Re: Having Issues With a Simple Calculation

I am still a novice with Power BI so may have to bare with me but I beleive I am creating a new measure that I wish to use in a matrix table on my report.

 

My [Offered] column comes from the 'All Phones' table

My [API] column comes from the 'Files Uploaded' table

 

I have seen other users make new measures from columns from different tables that work but it feels like I am falling at the first hurdle.

 

Smiley Indifferent

 

Hope that provides some clarity.

 

Kind regards

Paul

 

 

Super User
Super User

Re: Having Issues With a Simple Calculation

@PaulMac Could you please check and confirm what's the relationship between "All Phones" and "Files Uploaded" table. 



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





BobBI Member
Member

Re: Having Issues With a Simple Calculation

try  DIVIDE function instead of calculate.

 

Calls per 1000 = Divide([offered],[uploaded])*1000

 

Offered and Uploaded are the 2 measure and Calls per 1000 should also be a measure.

Thanks,

SS

Microsoft v-jiascu-msft
Microsoft

Re: Having Issues With a Simple Calculation

Hi Paul,

 

The final solution is based on your data model. But you can try it like below. CALCULATE isn't necessary in your scenario.

 

Calls per 1000 API Files Uploaded =
MIN ( 'All_Phones'[Offered] ) / MIN ( 'Files Uploaded'[API] )
    * 1000

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Microsoft v-jiascu-msft
Microsoft

Re: Having Issues With a Simple Calculation

Hi Paul,

 

Could you please mark the proper answers as solutions?

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
PaulMac Member
Member

Re: Having Issues With a Simple Calculation

Hi @v-jiascu-msft

 

I am afraid that was not the correct measure. Using your exampled yeilded a result of 0 across all rows.

 

If calls offered (value 21) is in column B and Files Uploaded (value 5,113) is in column D then I can do this simply in Excel with =B1/D1*1000. (21/5113)*1000=4.11

 

Why is this so hard to replicate as a dax measure?

 

Anyone else have any suggestions?

 

PaulMac

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,246)