Do I need variables?

Question

Hello folks,&nbsp;&nbsp; &nbsp;New BI user here but not new to analysis.&nbsp; 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.&nbsp; I exclusively used dynamic tables in Excel so referencing tables and columns is no problem for me.&nbsp;&nbsp;&nbsp;So my problem:&nbsp; &nbsp; 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.&nbsp; In Excel, I would typically just use&nbsp; =SUMIFS(xxxxx)/SUMIFS(xxxxx)&nbsp; 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.&nbsp;Here is where I am stuck:&nbsp; &nbsp; &nbsp;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)&nbsp;If I strip away the DIVIDE function, as well as one of the other SUMX's, I get the correct values.&nbsp; So I know the CALCULATE(SUMX functions are working.&nbsp; I just can't seem to get it to divide the two and come up with the correct value.&nbsp;Any help is greatly appreciated!

adambhappy · Answer

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.

adambhappy · Answer

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 · Answer

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

mikebravo · Answer

I'll agree with that.&nbsp;The table is quite large in column width and length so I'm not sure how much a snap shot would help you.&nbsp; Dist and TotalFuel are columns in the table that come directly from the SQL server.&nbsp; I created a table on a report tab and made the first column EquipmentID (truck number) which comes from the Omnitrack_Performance table.&nbsp; Within that table on the report tab, I added a measure called Trend which is where the above mentioned formula is stored.&nbsp; I've included a picture of that table.&nbsp;&nbsp; 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.&nbsp; We are trying to identify trucks in our fleet that decrease in MPG's more than 2 tenths of a MPG over one week.&nbsp; Hoefully this provides some context to the project.&nbsp; By the way, I really appreciate your help on this.&nbsp; Our IT and development team here has been zero help.&nbsp; 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.&nbsp;&nbsp;

mikebravo · Answer

&nbsp;&nbsp;I don't think the first picture worked.&nbsp; Let's try this.&nbsp;Also, I've noticed "Trend" shows up in the Omintrac_Performance table as a column.&nbsp; I t hought it was a measure but apparent its not.&nbsp; Should I start by changing that to a measure?&nbsp;&nbsp;&nbsp;Omnitrack_Performance TableReport Tab

Forum Discussion

Do I need variables?

5 Replies

Related content

dynamic library variables

Dynamic Variable in Calculated Columns

Resetting a string pipeline variable

Understanding variable calculation

OneDriveSharePoint shortcut with library variables error