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