I Need Help Converting a Measure from Excel to Power BI

[PaulMac]

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?

Many thanks in advance.

PaulMc

[edhans]

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.

[PaulMac]

Sorry @edhans 

This method did not work as intended.

Thanks for your assitance though

Paul Mc

[edhans]

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.

[PaulMac]

@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."

[edhans]

---

@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.

[PaulMac]

@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

[itchyeyeballs]

@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 ?

[itchyeyeballs]

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.

This link has a simple explanation - https://www.encorebusiness.com/blog/working-pivoted-data-power-bi/