Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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 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?
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.
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?
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |