cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mikebravo Frequent Visitor
Frequent 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 Regular Visitor
Regular Visitor

Re: Do I need variables?

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.

mikebravo Frequent Visitor
Frequent Visitor

Re: Do I need variables?

Ignorance possibly..?? Smiley Wink    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.  

adambhappy Regular Visitor
Regular Visitor

Re: Do I need variables?

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 Frequent Visitor
Frequent Visitor

Re: Do I need variables?

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.

 

 

mikebravo Frequent Visitor
Frequent Visitor

Re: Do I need variables?

  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.JPGOmnitrack_Performance TableTrend.JPGReport Tab

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 26 members 801 guests
Please welcome our newest community members: