cancel
Showing results for
Did you mean:

## I Need Help Converting a Measure from Excel to Power BI

Hello Community

I have the below DAX measure that I use to calculate the volume of YTD Complaints per 1,000 Transactions

`=SUMPRODUCT(\$D\$7:D14)/SUMPRODUCT(\$C\$7:C14)*1000`

Column D lists the number of complaints received per month and Column C list the number of transactions per month.

This is used in an excel table but I need to replicate this in Power BI. As there are no ranges in the Excel sense, how can I achieve this?

PaulMc

8 REPLIES 8
Super User III

Everything in DAX is in a table with records, but not rows and cell references in the same way Excel has them.

The general function you'll use is SUMX().

```New Total =
SUMX ( TableName, TableName[Column1] / TableName[Column2] * 1000 )```

However, you are doing a cumuluative formula based on the \$ references in your Excel formula. So the formula above would have to be modified to be someting along the lines of :

```New Total =
CALCULATE (
SUMX ( TableName, TableName[Column1] / TableName[Column2] * 1000 ),
TableName[ColumnABC] <= MAX ( TableName[ColumnABC] )
)```

Where ColumnABC is some column (date, invoice number, something that is incrementing) that the above measure would always get the data from the first row through the current row in the current context. It gets more complex depending on how your visual is laid out and you may need to also inclued an ALL() filter or similar to ignore the filter context to get the cumulative total.  See this article on help with cumulative/running totals.

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting

Sorry @edhans

This method did not work as intended.

Paul Mc

Super User III

Sorry @PaulMac . Best I could do with the info given. I'd need to see a model to actually understand how your data is structured. Hard to work with a spreadsheet formula with no other context.

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting

@edhans  The error I got was:

"A function 'MAX' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

Super User III

@PaulMac wrote:

@edhans  The error I got was:

"A function 'MAX' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

Ok. That can be fixed with a CALCULATE and FILTER statement, but I cannot guess anymore at the model. Someone else can jump in, or I'll be happy to try and tackle it if I have real data to play with. I'm spending more time speculating on the model than I am troubleshooting the actual issue.

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting

@edhans Thank you for assitance Ed, it is very much appreciated.

I understand that it must be diffult to diagnose a problem/issue if you can't see said issue.

But due to sensitivity of the data I cannot release it externally and do not posess the time at present to complile a dummy set of this model.

If I am able to produce some dummy data to mirror my issue then I will post this here.

Many thanks again

Regards

PaulMc

Post Prodigy

@PaulMac PowerBI is a different world to "classic" Excel formulas, unless you get your data in shape it will be an uphill battle to get the results you need.

As an aside, can I ask why you are using the sumproduct formula with only a single array? my understanding is that's functionally equivalent to sum() which would be conceptually, much easier to implement in PowerBI ?

Post Prodigy

You will probably need to look at how your data is structured before you start writing any DAX as Power BI works best with rows rather than columns when aggregating.

Announcements