Converting Excel Formulas to DAX

when your DAX calculation is to much complicated to create and cause perfomance issue, try to push down the complexity in your ETL in order to transform and expose a comprehensive oriented analytical model. 

Data preparation is the key to build and effective and performant analytical model.

This have been always the case on a standard BI Project, and still the same on BI Self-Service.

In your case I would have precaculated some metric with Power Query to create by the end a very simple DAX formula.

I still cannot get the last formula to work. I don't like the alternative of using regular excel formulas in a cell separate from the actual pivot table as the data is a full refresh every day and filling down/up will require a macro to automate but I am trying to stay away from any macros or controls. 

Can someone let me know if it is not possible to achieve this problem using either a measure or calculate column so that I can look into an alternative method and close this posting? 

Thanks in advance

greggyb fbrossard Thank you both for the suggestions. I have moved the majority of my former measures upstream to where it calculates before it hits the data model. For now, I am only using Steve, Sysco, Impact, and WHB as measures in the data model.

I have also created an index column to handle the sorting, however, I get a semantic error for the WHB formula:

WHB = 
CALCULATE(
        DIVIDE(
                SUM( SomeDamnTable[Fred] )
                ,SUM( SomeDamnTable[Allen] )
        )
        ,ALL( SomeDamnTable )
) - CALCULATE(
        SUM( SomeDamnTable[Impact] )
        ,FILTER(
                ALL( SomeDamnTable )
                ,SomeDamnTable[Index] <= EARLIER( SomeDamnTable[Index] )
        )
 ) 

"Earlier/Earliest refers to an earlier row context which doesn't exist." 

However, the sorting can be done manually as long as the WHB formula works. 

Thanks in advance

WHB was defined as a calculated column in my model. That error indicates you're using it as a measure.

Use MAX(), which will be evaluated in the filter context of the visualization, rather than EARLIER() which depends, as the error indicates, on a row context that cannot exist in the top level of a measure evaluation in a visualization.

I've not put much thought toward using these as measures, but I can't see much issue. Subtotals will be calculated as of the item in that subtotal with the greatest value for [Index].

Should IMPACT also be used as a Calculated Column?

I am asking because WHB as a calculated column throws out an error saying "the sum function only accepts a column reference as the argument number 1."

The error highlights the IMPACT measure, however, moving IMPACT to a calculated column results in the same number in every row. 

1) Impact is not the same for every row. The .pbix file I shared has identical results to your expected results sample in this thread; the last two rows are 0, the rest are -9.09%.

2) What are your reporting requirements? Whether these should be measures or columns in the model depends on that.

You wanted an output table identical to Excel, and I can do that with very little understanding of your problem. Answering any other questions will be an exercise in frustration for all if we're not both clear on the requirements for the final solution.

greggyb The reporting requirement just asks for accurate data so it does not matter if it is a measure or column as long as the correct results are reached. I am currently using the data model in Excel to prepare the data for PowerBI when we transfer our reports over to PowerBI. Everything is working except for the WHB formula. That is the final piece of the puzzle. 

Accurate data is not a sufficient requirement.

Will reporting always be at the grain of the table, or will we expect to further summarize these values? What should interaction with slicers and filters look like?

If it's all detail reporting (at the table grain in the sample you've provided), then you can and probably should persist everything in the table (and that table should probably be in SQL Server - DAX and Tabular are analytical technologies, not terribly well suited to detail-level reporting).

If, instead these values must be aggregated, we need to understand what aggregation looks like. Does it make sense to do simple sums, averages, maxes, mins, etc on these values as calculated columns, or should the numerators and denominators be recalculated based on filter context?

Accurate implies different requirements based on the answers to the above.

Yes, the reporting will always be at the grain of the table. This data will only be used for report and is separate from everything else so simple sums, averages, maxes, etc will work. 

Then choose among the Power Query code and DAX calculated columns in the .pbix file I shared. Better to represent these as columns.

Great, thanks for all the help. I really appreciate it. Your first post in the thread really answered a lot of questions so I'll mark that as the answer.