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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PaulMac
Helper IV
Helper IV

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
v-jiascu-msft
Employee
Employee

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.
v-jiascu-msft
Employee
Employee

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.

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

_donev
Regular Visitor

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. 

Hi @_donev

 

No, my data looks like the picture below:

 

Capture.PNG

 

Any thoughts?

 

Kind regards

Paul

 

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

@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?

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

 

 

@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 PBI Community Champion




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.