Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |