Error in DAX formula involving Variable & AverageX

[bitbit]

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

)

)

[Anonymous]

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]

Hi Darek,

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

Country	Actual	Budget	Prior Year
CountryA	293	249	282
CountryB	500	488	483
CountryC	481	427	445

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

Country	Actual	Budget	Prior Year
CountryA	48113	43088	44088
CountryB	13941	15881	14259
CountryC	17411	18426	18101

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

Country	Actual	Budget	Prior Year
CountryA	1342	1576	1394
CountryB	786	806	813
CountryC	816	921	882

DAX formula should produce production volume per DL as follows:

Country	Actual	Budget	Prior Year
CountryA	36	27	32
CountryB	18	20	18
CountryC	21	20	21

[Anonymous]

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]

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

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

[Anonymous]

Hi.

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

Best

Darek