Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Hi Paul,
Could you please mark the proper answers as solutions?
Best Regards,
Dale
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
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
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.
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
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.
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.
Proud to be a PBI Community Champion
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |