cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bitbit Regular Visitor
Regular Visitor

Error in DAX formula involving Variable & AverageX

Pls advise how to fix syntax error in below formula:

Prodvol per DL =
VAR
prodvol = CALCULATE(sum(data[Tons production],data[date])
VAR
DLavg = AVERAGEX(filter(FTE,FTE[Line]="Total direct manufacturing personnel"&&FTE[FTECat]="FTE Total"),'FTE'[Value])
)
RETURN
    if(
isblank(DLavg),"0",
divide(
prodvol,avgDL
)
)
5 REPLIES 5
Super User
Super User

Re: Error in DAX formula involving Variable & AverageX

 

Prodvol per DL =
VAR prodvol =
    CALCULATE ( SUM ( data[Tons production] ) )
VAR DLavg =
    AVERAGEX (
        FILTER (
            FTE,
            AND( 
           	FTE[Line] = "Total direct manufacturing personnel",
            	FTE[FTECat] = "FTE Total"
            )
        ),
        'FTE'[Value]
    )
RETURN
	DIVIDE ( prodvol, avgDL, 0 )

Please, do yourself a favour if you don't want to get stuck trying to figure out where you've messed up the syntax: ALWAYS format your code correctly according to the rules. If you have trouble, use www.daxformatter.com.

 

Best

Darek

 

bitbit Regular Visitor
Regular Visitor

Re: Error in DAX formula involving Variable & AverageX

Hi Darek,

 

Although syntax error is resolved, the calculated result is incorrect:

CountryActualBudgetPrior Year
CountryA293249282
CountryB500488483
CountryC481427445

 

Dataset "data", calculated production volume ytd Jun (NB: budget and prior year data includes all 12 months):

CountryActualBudgetPrior Year
CountryA481134308844088
CountryB139411588114259
CountryC174111842618101

 

Dataset "FTE", calculated average direct labour headcount ytd Jun (likewise, budget and prior year data includes all 12 months):

CountryActualBudgetPrior Year
CountryA134215761394
CountryB786806813
CountryC816921882

 

DAX formula should produce production volume per DL as follows:

CountryActualBudgetPrior Year
CountryA362732
CountryB182018
CountryC212021
Highlighted
Super User
Super User

Re: Error in DAX formula involving Variable & AverageX

Sorry, mate, I can't help you since I don't know anything about your model. I showed you where the syntax error was. That's all I could do. If you need to have some logic in your measures, you have to show all the details. I'm not able to solve an equation if I don't even know what it looks like.

 

Best

Darek

bitbit Regular Visitor
Regular Visitor

Re: Error in DAX formula involving Variable & AverageX

Hi Darek.  Pls refer to the sample pbix file in below link:

https://www.dropbox.com/s/3h85fs98rx4cq8l/sample.pbix?dl=0

 

Super User
Super User

Re: Error in DAX formula involving Variable & AverageX

Hi.

 

I'll have a look when I get a chance... But be prepared to answer questions.

 

Best

Darek

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 119 members 1,463 guests
Please welcome our newest community members: