Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mikebravo
Regular Visitor

Do I need variables?

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!
5 REPLIES 5
adambhappy
Resolver II
Resolver II

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?

 

 

 

Omnitrack_Performance TableOmnitrack_Performance TableReport TabReport Tab

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.