Solved: Re: Converting Excel Formulas to DAX

GTR · ‎12-08-2015

Hi there, I am trying to convert an Excel document into my data model and am having trouble with absolute referencing. Here is a screenshot of the data&colon;

Columns H and J are the I am having trouble with. For Column H, how do I reference the totals of columns E,C, and B while getting the rows for row G? I assumed it would be the sum function of that column "Sum(Impact)" but the end result is incorrect. I have tried using the Earlier function but need some direction to solving this problem. The calculated field/measure gives me incorrect numbers when it does work. I also need additional help with Column J which I have yet to pursue as hard since it uses Column H.

Notes:

All fields are numerical
Data has been hidden for privacy reasons
I have created the working formulas as calculated columns but did briefly experiment with calculated columns, however the datasheet will be long so I do not want the column to calculate for all rows but if it will fix the formula, I will work with that as well.

Any help would be appreciated in leading me in the right direction, thanks in advance.

greggyb · ‎01-08-2016

Didn't have time to read the whole thread, but your WHB is impossible in DAX as you've got it defined. Luckily we can redefine it in a much more reasonable way (in terms of fields being accessed). Here are all the measures in clean DAX for you. It should be a good exercise in observing row vs filter context.

DAX isn't really the tool of choice for this, though. This sort of data munging should be done before the model is loaded. Unless you want these as measures, in which case, most of the ALL()s should be replaced with ALLSELECTED(), and the raw column references will have to be wrapped in SUM()s.

Steve = 
DIVIDE( SomeDamnTable[Fred], SomeDamnTable[Allen] ) // All row context

Sysco = 
DIVIDE(
	SomeDamnTable[Fred] + SomeDamnTable[X] // All row context
	,SomeDamnTable[Allen]
)

Mary = 
IF(
	SomeDamnTable[Fred] >= SomeDamnTable[Allen] // All row context
	,0
	,SomeDamnTable[Allen] - SomeDamnTable[Fred]
)

Impact = 
CALCULATE( // Do this whole thing in a filter context made up of the entire table
	DIVIDE(
		SUM( SomeDamnTable[Fred] )
		,SUM( SomeDamnTable[Allen] )
	)
	,ALL( SomeDamnTable )
) - DIVIDE(
		CALCULATE( // this CALCULATE is in a filter context of the entire table
			SUM( SomeDamnTable[Fred] )
			,ALL( SomeDamnTable )
		) + SomeDamnTable[Mary] // This is row context
		,CALCULATE( // this calculate is in the filter context of the whole table
			SUM( SomeDamnTable[Allen] )
			,ALL( SomeDamnTable )
		)
	)
		

Bob = 
IF( // all row context
	( SomeDamnTable[Fred] + SomeDamnTable[X] ) >= SomeDamnTable[Allen]
	,0
	,SomeDamnTable[Allen] - SomeDamnTable[Fred] - SomeDamnTable[X]
)

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

You should try to do this sort of thing before your data hits your model. Here's some Power Query to get you there. You can examine all this in the .pbix here.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAWMDMI7ViVYywhAxxhAxwRAxxRAxwxAxxxCxwBCxxBAxNIAKGSKEDFGFYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Product = _t, Allen = _t, Fred = _t, X = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", Int64.Type}, {"Allen", Int64.Type}, {"Fred", Int64.Type}, {"X", Int64.Type}}),
    Steve = Table.AddColumn(#"Changed Type", "Steve", each [Fred] / [Allen]),
    Sysco = Table.AddColumn(Steve, "Sysco", each ( [Fred] + [X] ) / [Allen]),
    Mary = Table.AddColumn(Sysco, "Mary", each if [Fred] >= [Allen]
then 0
else [Allen] - [Fred]),
    Impact = Table.AddColumn(Mary, "Impact", each ( List.Sum( Mary[Fred] ) / List.Sum( Mary[Allen] ) )
- ( ( List.Sum( Mary[Fred] ) + [Mary] )
  / List.Sum( Mary[Allen] ) )),
    Bob = Table.AddColumn(Impact, "Bob", each if ( [Fred] + [X] ) >= [Allen]
then 0
else [Allen] - [Fred] - [X]),
    Index = Table.AddIndexColumn(Bob, "Index", 1, 1),
    WHB = Table.AddColumn(Index, "WHB", each let
  CurrentRow = [Index]
  ,Base = 
    ( List.Sum( Index[Fred] ) 
      / List.Sum( Index[Allen] ) )
  ,SumImpact =
    List.Sum(
      Table.Column(
        Table.SelectRows( Index
          , each [Index] <= CurrentRow )
        ,"Impact" ) )
  ,WHB = Base - SumImpact
in
  WHB),
    #"Removed Columns" = Table.RemoveColumns(WHB,{"Index"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Impact", Currency.Type}, {"WHB", type number}, {"Mary", Int64.Type}, {"Sysco", Int64.Type}, {"Steve", Int64.Type}, {"X", Int64.Type}, {"Fred", Int64.Type}, {"Allen", Int64.Type}, {"Product", Int64.Type}, {"Bob", Int64.Type}})
in
    #"Changed Type1"

Check out the Power BI User Group of Philadelphia
Next meeting: October 5, 2016 Virtual meeting

View solution in original post

Greg_Deckler · ‎12-08-2015

Make sure that you use SUMX with an ALL filter for your measures (the equivalents of your row 14) I expect that what you are seeing is that when you put the measures in a table or use them in a column, that they are being context filtered by the row they are in and that is messing up your result. Just spit-balling since I can't see what you are seeing and do not have any sanitized sample data with the correct results desired to go off of.

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

GTR · ‎12-08-2015

Unpivoting the data is not a long-term option at the moment.

smoupre, I tried your logic and get error (second argument contains a string error) on the ALL function. Here is what I have:

=SUM([STEVE]) - (([SumFred] + SUMX(TABLENAME,[MARY])) / [SumAllen])

SumFred and SumAllen are just separate measures that give me the sums for the two columns, althought the sum(Fred) would also work, just using it for testing at the moment.

The result I get for this measure is "-100.00%" for all rows which is obviously incorrect. I should be getting "-9.09%"

Thanks

kcantor · ‎12-08-2015

I would recommend creating seperate measures and then using the divide function instead of trying to reference so many columns in the same measure. Build each measure then divide one measure by the other.

=DIVIDE([totalFredMary],[Allen]) of course you should use actual measure names, I just threw those together.

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

GTR · ‎12-09-2015

I broke the formula down to three different measures but still get the wrong values (-900.00%) for the rows. This is using the SUMX and ALL as suggested above.

itchyeyeballs · ‎12-09-2015

Is it just a single table in your model or do you have links?

GTR · ‎12-09-2015

It is just a single table that I condensed down to show in this post. My data actually lives in Access and I created connection to this workbook so relationships are not an issue. I am simply trying to change the formulas into measures. I am getting pretty close but somewhere along the lines, I don't think it likes changing the end result into a percent data type from a whole number/decimal.

I broke out the individual formulas one by one and created measures. The individual measures work until I get to the final measure which subtracts a number that resulted from the divide function. Instead of getting -9.09%, I receive -900.00%. Also, I converted the result to a decimal to get around it but it gets -900.00. Multiplying the result by 100 getts -9.00 which is still .09 off.

Thanks

Greg_Deckler · ‎12-10-2015

Honestly, this is pretty much impossible to troubleshoot without some kind of sample data to play with.

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

GTR · ‎12-10-2015

Here is the data

Product	Allen	Fred	X	Steve	Sysco	Mary	Impact	Bob	WHB
1	1	0	0	0%	0%	1	-9.09%	1	27.27%
2	1	0	0	0%	0%	1	-9.09%	1	36.36%
3	1	0	0	0%	0%	1	-9.09%	1	45.45%
4	1	0	0	0%	0%	1	-9.09%	1	54.55%
5	1	0	0	0%	0%	1	-9.09%	1	63.64%
6	1	0	0	0%	0%	1	-9.09%	1	72.73%
7	1	0	0	0%	0%	1	-9.09%	1	81.82%
8	1	0	0	0%	0%	1	-9.09%	1	90.91%
9	1	0	0	0%	0%	1	-9.09%	1	100.00%
10	1	1	0	100%	100%	0	0.00%	0	100.00%
11	1	1	0	100%	100%	0	0.00%	0	100.00%

Total	11.00	2	0	18.18%	18.18%	9.00

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

The IMPACT and WHB are the two measures I am having troubles with. The measures for Steve, Sysco, Mary, and Bob are already done with no problem. WHB reflects on the IMPACT column being correct.

Let me know if you need any additional information. Keep in mind that this table has over 500,00 rows, I condensed it down for testing purposes.

Thanks

itchyeyeballs · ‎12-10-2015

How about generating a totals table on import then linking to the original table via a common products lookup table? Turns this into a granularity problem and there are plenty of examples for that out there.

GTR · ‎12-11-2015

I already have a totals measure for all of the fields being used.

itchyeyeballs · ‎12-11-2015

I'll bow out of this one then, can't visualise why you are doing things this way, a dax expert will no doubt be along soon to solve the issue although I still think sorting your model would make things more straight forward.

GTR · ‎12-14-2015

I have tried every suggestion and figured out it fails on the =sum([Fred])+sumx(table,Mary) measure. The measure is suppose to ADD the TOTAL for FRED with the ROWS (not total) for MARY.

Total for Fred = 2

Row for Mary = 1

Total for Fred + Mary = 3

For example, in the first row, it should calculate 2+1=3, however, I am getting 31 as a result or 11 when using different methods.

I hope this helps.

GTR · ‎12-15-2015

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

kcantor · ‎12-17-2015

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.

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

GTR · ‎12-17-2015

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

kcantor · ‎12-17-2015

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/

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

GTR · ‎12-17-2015

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

Here is the data

Product	Allen	Fred	X	Steve	Sysco	Mary	Impact	Bob	WHB
1	1	0	0	0%	0%	1	-9.09%	1	27.27%
2	1	0	0	0%	0%	1	-9.09%	1	36.36%
3	1	0	0	0%	0%	1	-9.09%	1	45.45%
4	1	0	0	0%	0%	1	-9.09%	1	54.55%
5	1	0	0	0%	0%	1	-9.09%	1	63.64%
6	1	0	0	0%	0%	1	-9.09%	1	72.73%
7	1	0	0	0%	0%	1	-9.09%	1	81.82%
8	1	0	0	0%	0%	1	-9.09%	1	90.91%
9	1	0	0	0%	0%	1	-9.09%	1	100.00%
10	1	1	0	100%	100%	0	0.00%	0	100.00%
11	1	1	0	100%	100%	0	0.00%	0	100.00%

Total	11.00	2	0	18.18%	18.18%	9.00

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!

kcantor · ‎12-17-2015

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.

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

GTR · ‎12-17-2015

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.

GTR · ‎01-08-2016

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

Converting Excel Formulas to DAX

Helpful resources

Fabric Community Update - July 2025

Power BI Monthly Update - July 2025

How to Get Your Question Answered Quickly

Party with Power BI’s own Guy in a Cube

Converting Excel Formulas to DAX

Helpful resources

Fabric Community Update - July 2025

Power BI Monthly Update - July 2025

How to Get Your Question Answered Quickly