Converting Excel Formulas to DAX

Just came back from DAX training and I can tell you that any time you reference a cell . . . it will not work. The powerPivot engine (BI as well) does not work with cell references. In fact, each "cell" is calculated seperately and independently as an island of data. You need to create a measure to get the data you are seeing in 'A:1' then reference that measure in your dax instead of the cell.  I haven't gone back and re read this post to see which expression you are working on but as soon as I get to a larger screen I will revisit this if you haven't solved it yet.

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 have figured out how to get Column H (IMPACT) to work. Now I need help on the final column I (WHB).

Is the formula for column WHB possible since it is using A1 referencing and has two different formulas for the column? Row 1 uses a different formula than the rest of the rows. I was thinking an IF statement of some kind may work since the top row is the MAX of Mary with the data being sorted largest to smallest by Mary. 

Any help would be appreciated. I cannot find any blog posts or book references on this issue.

Please let me know if this can be done as a measure or calculated column.

Thanks

Thank you for the info, I have solved the IMPACT column but not the WHB column. I have been reading the DAX Formulas book and they do point out that 'A1' referencing is no more but I am still looking for a way to get around it. 

Thanks

I glanced back and did not see the formula you are working on. Can you post the specific formula? I would love to take a crack at it. I love a good puzzle.

Check out this reference:  http://www.powerpivotpro.com/2015/10/giving-back-steal-this-reference-card/

kcantor I have that reference card right in front of me laminated and all. 

Here is the data

Here's the formulas I am trying to turn into measures in the data model:

(sorry for the small image, might need to zoom in as this is the largest it would let me attach) 

I have the IMPACT column figured out using calculated columns in my measures to achieve the result. I am also confident that the WHB can be done with an "IF" measure. I already have the first part partially working. I am struggling with the second part of the IF.

First Part: 

test1 = SUMX (TABLE, [IMPACT] * -1)    ......."Turns values into positive numbers"

and

test2 = ([STKIMPACT]) + [test1]

FYI:

STKIMPACT:= SUM([FRED])/SUM([ALLEN])

MARY:= IF([Sum of FRED]>[Sum of ALLEN],0,[Sum of ALLEN]-[Sum of FRED])

Second Part (adding previous row of IMPACT column):

It is important to note that ONLY the MAX of column MARY uses test2 in the IF statement. I have this part partially working, however, there are multiple rows tied for the MAX so it is doing this formula for all of the MAX rows instead of just the first row which is what I am needing.

Along with this, I need the ELSE part of the IF statement which is using the previous row of IMPACT and adding it to the current row. 

This is the formula I have but is not correct since the second part does not have a measure yet:

= IF (MAXA ([MARY]), [test2], [need this formula as described above])

Thank you in advance for your help!

Okay, I may be able to offer a little help. Look up the CURRENTROW Function and apply it to the MAXA for Mary.  Again, I have limited access right now but I am intrigued and will be revisiting this when I have my computer in front of me instead of my phone. You might also be interested in "LookingBack" and EARLIER/EARLIEST to help with the Excel type references.

I have tried the EARLIER/EARLIEST but do not have a index column to reference it to at the moment but will explore it at a later time. I am still reading up on these functions and will take a closer look at them tomorrow. 

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.