Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
In the Excel, I am using the formula =IF((D4-D5)=(C5-C4),"OK",((D4-D5)/(C5-C4))) to calculate the productivity for each week. However in Power BI I am unable to locate any such option to select individual cell. Can anyone please suggest how I can run this formula in BI please?
1st Coloumn= Date, 2nd - Orig Estimation, 3rd aka C - Completed Work, Coloumn 4 aka D is Remaining days
24 February 2020 | 1670.5 | 2017 | 418.5 | 765 | 2435.5 |
02 March 2020 | 1670.5 | 2122.75 | 352.75 | 805 | 2475.5 |
09 March 2020 | 1670.5 | 2222.25 | 299.5 | 851.25 | 2521.75 |
16 March 2020 | 1320.5 | 2082.25 | 153 | 914.75 | 2235.25 |
All I want a value subtraction from 299.5-153 and 914.25 -854.75 in a new coloumn. Any suggestion
Also, is there any way in which I can add information manually in the BI instead of adding CSV or excel file every week. My data only changes once in a week. However, I have to load entire CSV file every week to analysis.
Please suggest,
Solved! Go to Solution.
Hi @Anonymous ,
You could refer to my sample for details.
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
If you want to get result of current row-previous row, you could refer to my sample for details. If not , please correct me and inform me more detailed information(such as your expected output and your sample data (by OneDrive for Business))? Then I will help you more correctly.
Please do mask sensitive data before uploading.
Thanks for your understanding and support.
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
https://1drv.ms/x/s!ApDgFRXBWrzSp313Wm95IfWWiRqw?e=20MDyz
Hi Zoe,
I have attached the excel sheet. Basically I am looking for the formula in Column G, H and I. Any help would be much appreciated. At the time being we can overlook formula in column F.
The formula in column E named as 'Overspend' is easy. I reckon I would be able to do it.
Thank you again,
Regards,
Amit
Hi Zoe,
Can you please explain me the logic behind this formula
= Table.TransformColumnTypes(Source,{{"Date", type date}, {"Original Estimate", Int64.Type}, {"Completed Work", Int64.Type}, {"Remaining Work", Int64.Type}, {"Overspend", Int64.Type}, {"Productivity ", Percentage.Type}, {"Work done in Completed work each week", Int64.Type}, {"Work done in Remaining Work each wek", Int64.Type}, {"Productivity each week", Percentage.Type}})
Thank you 🙂
Hi @Anonymous ,
This is code to change data type to what you want, you could see it in preview(they show as int or percentage )
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Dear Zoe,
I agree with you. Those are integer. However, I need to konw the formula or logic behind how did you substracted value from data.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.