Row-wise evaluation of formula before summarization

[Anonymous]

Hi,

I would like to calculate a measure based on column data and What-If-Parameter input. In my case, I have a What-If-Parameter "Financial Cap" and another one "Base Value". In addition, I have a table with two columns, ID and Relevant Compensation. 

In Excel I would do something like this but I am not able to replicate this in Power BI:

1. Add a new column
2. Add the formula IF((Relevant Compensation * 0.5 + Base Value)>Financial Cap,Financial Cap,(Relevant Compensation * 0.5 + Base Value))
3. Sum up the new column

As far as I know, calculated columns cannot work with What-If-Parameters. However, a measure itself cannot do this row-based evaluation before summing it up, at least not that I know. Do you have any suggestions how to solve this? I have tried various functions but I am not getting anywhere.

[V-lianl-msft]

Hi @Anonymous ,

Based on your description, I have created two measures, one is to calculate the formula you referred, and the other is to calculate the sum.

Sample .pbix

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mahoneypat · Accepted solution]

I was working on this before I saw the answer about SUMX. That's how you'd do with SUMX for your parameters.

SumX Example of a 'Financial Capitalization Value'[/selection of store financial capitalization

var selectedbasevalue a 'Base value'[Base value] //store base value selection

var sumxresult á sumx('Table', var comparisonvalue á 'Table'[Relevant Compensation]*0.5+selectedbasevalue //make a variable at iterated row level so don't have to calculate ittwice per iteration

return if(comparisonvalue>selectedcap, comparisonvalue)) // use the variable in both parts of IF()

return sumxresult

If this works, mark it as the solution. Congratulations are also appreciated.

Best regards

Pat

[mwegener]

Hi @Anonymous ,

take a look at SUMX

Returns the sum of an expression evaluated for each row in a table.

https://docs.microsoft.com/en-us/dax/sumx-function-dax