cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PaulMac
Advocate II
Advocate II

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 8
edhans
Super User III
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 I answer your question? Mark my post as a solution!
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.

 

Thanks for your assitance though

 

Paul Mc

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 I answer your question? Mark my post as a solution!
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."

 

 


@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 I answer your question? Mark my post as a solution!
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

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

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/

 

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.