Forum Discussion
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?
Many thanks in advance.
PaulMc
8 Replies
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.
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/
