Excel in Dax

[Joak]

Hello everyone, 

I'm trying to produce a new report using power BI and DAX language, however I can't find a way to reach what I want. 

After reading some problems and their solutions, I haven't find any similar.

I'd like to build a new column, where I sum two different rows in two different lines, based on multiple criterias.

Here is the formula used in excel:

=IF(AND(D29="xxx";A29=A30);(H29+H30à/MIN(I29;I30);"")

With a key column in A to group all lines with the same criterias.

How would you do that in dax ?

Thanks

[Anonymous]

The short answer is "You Don't"

The longer answer is to figure out what exactly you are doing in this calculation and figure out the best logic to do this with either DAX or Power Query.

For example, if your calculate is very much a "This row and the previous row" then you are going to have to do this within Power Query as part of your data import.  This requires you to create a sequential number row column and write a statement that makes use of the row number as part of the calculation.

If your logic is simply about adding up some values between rows that have some correlation, you can make use of a calculate statement to do this for you.  Something like:

Your Column = 
VAR YourCriteria = [CriteriaField]
RETURN

CALCULATE(
	SUM('YourTable'[Summing Field']),
	ALL('YourTable'),
	'YourTable'[CriteriaField2] = YourCriteria
)

This would give a row by row sum where the values in [CriteriaField2] match what is in the current rows [CriteriaField].

[Joak]

Thanks for your answer. 

I managed to find what I was searching for, using another formula.

You're right, my calculate is very much a "this row and the previous row", however, each row has a specific criteria and regarding the amount of data I can't use such a formula. Also I wanted to have a sum of two average, here what I did:

To succeed I created two new columns, excluding all the values except those which were matching my criteria (using = IF('Table'[xxx]="abcd";'Table'[yyy];0) ). In addition to that, I created a matching key (='Table'[zzz]&'Table'[www]&'Table[vvv])

Then, I wrote a measure 

Average of 'Table'[uuu] by MatchingKey =
AVERAGEX(
KEEPFILTERS(VALUES('Table'[MatchingKey]));
CALCULATE(SUM('Table'[uuu]))
)

This made the sum of the average of values matching the same criterias. 

I don't really know whether this is the simpliest solution but it works 🙂

[v-jiascu-msft]

Hi @Joak,

Could you please mark the proper answer as solution if it's convenient for you? That will be a help to the others.

Best Regards!
Dale