Forum Discussion
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
I assume your data looks like the picture below:
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 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] ) * 1000Best Regards,
DaleI 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
Hi Paul,
Could you please mark the proper answers as solutions?
Best Regards,
Dale
