Forum Discussion
My Measure/new Column won't work
Hello! I have a quick question... I have two tables that are connected by a many to many relationship. I am trying to create some measures of hand calculations but I'm a bit stuck.
This is what I have right now.... Two tables: Downtime Report and OR Report
This table includes: Downtime Report[Date], Downtime Report[Plant and Line], OR Report [ Planned OR%], OR Report [Line Result], Downtime Report[ Total Assembly DT Min], Downtime Report[Line Stop Downtime - NAM Ops - Operations DT Mins], Downtime Report[% of DT Minutes]
I now need to create a complicated formula to create a new percentage, but it needs the percentages from the OR Report table, and the % of DT Minutes from the Downtime Report. Here is the formula that I'd need to create:
Adjusted OR:
= if(
if(( OR Report [Line Result] - OR Report [ Planned OR%]) < 0 , OR Report [Line Result] - (OR Report [Line Result]- OR Report [ Planned OR%]) * Downtime Report[% of DT Minutes] , OR Report [Line Result] + (OR Report [Line Result] - OR Report [ Planned OR%]) * Downtime Report[% of DT Minutes]) > 100%, 100%,
if (( OR Report [Line Result]- OR Report [ Planned OR%]) < 0, OR Report [Line Result]- (OR Report [Line Result]- OR Report [ Planned OR%]) * Downtime Report[% of DT Minutes], OR Report [Line Result]+ (OR Report [Line Result]- OR Report [ Planned OR%]) * Downtime Report[% of DT Minutes])
Is this possible??
2 Replies
Hi nataliesmiy1357 ,
Thanks for reaching out to us with your requirement. Base on your description, there are two tables: 'Downtime Report' and 'OR Report'. And you want to get the percentage base on the following calculation logic. Am I right?
Adjusted OR:
= if(
if(( OR Report [Line Result] - OR Report [ Planned OR%]) < 0 , OR Report [Line Result] - (OR Report [Line Result]- OR Report [ Planned OR%]) * Downtime Report[% of DT Minutes] , OR Report [Line Result] + (OR Report [Line Result] - OR Report [ Planned OR%]) * Downtime Report[% of DT Minutes]) > 100%, 100%,
if (( OR Report [Line Result]- OR Report [ Planned OR%]) < 0, OR Report [Line Result]- (OR Report [Line Result]- OR Report [ Planned OR%]) * Downtime Report[% of DT Minutes], OR Report [Line Result]+ (OR Report [Line Result]- OR Report [ Planned OR%]) * Downtime Report[% of DT Minutes])
Could you please provide some raw data in your tables (exclude sensitive data) with Text format and your expected result with special examples and screenshots? And is there any relationship between these two tables? If yes, please provide the relationship info. It would be helpful to find out the solution. You can refer the following link to share the required info:
How to provide sample data in the Power BI Forum
And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
Here is a screenshot of the data that I'm playing with:
I created a unique id to link the two tables. It's the date and plant name:
