Do I need variables?

[mikebravo]

Hello folks,

   New BI user here but not new to analysis.  I'm trying to transition myself into BI and out of Excel as I have reached the limits if Excel with the work that I do but I am hampered by the varrying syntax between the two.  I exclusively used dynamic tables in Excel so referencing tables and columns is no problem for me.  

So my problem:

    I'm trying to find the sum of one column based on a row value and divide that with sum of another column based on that same row value.  In Excel, I would typically just use  =SUMIFS(xxxxx)/SUMIFS(xxxxx)  The overall lfunction will be more indepth and complex than that because I will build in dates and trends over dates into it at some point but I need to start with the basics.

Here is where I am stuck:

Trend = DIVIDE(CALCULATE(SUMX(FILTER(Omnitrack_PerformanceMetrics,Omnitrack_PerformanceMetrics[EquipmentID]=Omnitrack_PerformanceMetrics[EquipmentID]),Omnitrack_PerformanceMetrics[Dist])),CALCULATE(SUMX(FILTER(Omnitrack_PerformanceMetrics,Omnitrack_PerformanceMetrics[EquipmentID]=Omnitrack_PerformanceMetrics[EquipmentID]),Omnitrack_PerformanceMetrics[Totalfuel])),0)

If I strip away the DIVIDE function, as well as one of the other SUMX's, I get the correct values.  So I know the CALCULATE(SUMX functions are working.  I just can't seem to get it to divide the two and come up with the correct value.

Any help is greatly appreciated!

[adambhappy]

Why are you using SUMX() for this particular exercise instead of just a simple CALCULATE() with a SUM() inside? The filtering you do doesn't seem to require that the formula is evaulated at row context.

[mikebravo]

Ignorance possibly..?? 😉    In earlier trials, I was using CALCULATE and SUM with FILTER and EARLIER.  This is a simplified version as there will be 3 criteria for each function so I thought maybe I shoudl use SUMX.  

[adambhappy]

The context transition is one of the hardest things to get your head around in terms of DAX, it takes a while so not surprising.

Can you share a print screen of your tables and maybe a small sample set of the data?

Can I assume that [Dist] and [Totalfuel] refers to measures and not columns?

[mikebravo]

I'll agree with that.

The table is quite large in column width and length so I'm not sure how much a snap shot would help you.  Dist and TotalFuel are columns in the table that come directly from the SQL server.  I created a table on a report tab and made the first column EquipmentID (truck number) which comes from the Omnitrack_Performance table.  Within that table on the report tab, I added a measure called Trend which is where the above mentioned formula is stored.  I've included a picture of that table.

  If it hasn't registered, Dist is the amount of miles one of our units has traveled over a specified time and TotalFuel is the amount of gallons of fuel consumed in that time.  We are trying to identify trucks in our fleet that decrease in MPG's more than 2 tenths of a MPG over one week.  Hoefully this provides some context to the project.  By the way, I really appreciate your help on this.  Our IT and development team here has been zero help.  We have a company initiative to move our analyst departments to BI which I help manage but we have no support in training....so I'm blindly leading my team.

[mikebravo]

  I don't think the first picture worked.  Let's try this.

Also, I've noticed "Trend" shows up in the Omintrac_Performance table as a column.  I t hought it was a measure but apparent its not.  Should I start by changing that to a measure?

Omnitrack_Performance TableReport Tab