SUMX not calculating correct value for one month

[mukhan169]

I have a measure that calculates the average if only one value is selected from the drop down. if there are multiple values selected then calculate the average for each department individually and then add them together.

Following is the measure

Sum of Averge for past month =

var SimpleAverage= calculate (

AVERAGE(vwPowerBiData[ValueInUSD]),

FILTER(vwPowerBiData,vwPowerBiData[IsCurrentMonth]=False && vwPowerBiData[LineItemId]=1)

)

return

if(HASONEVALUE(vwDivision[Duns]),

SimpleAverage,

sumx(VALUES(vwPowerBiData[DivisionName]),SimpleAverage)

)

It calulates sum correctly for Jan, Feb and March 
January

But when I try and do it in April the sum is I am not correct I cant even figure out what and how it is calculating

it should be 2537. 
I am not sure what to do. Any help will be greatly appriciated.

[Anonymous · Accepted solution]

[Your Measure] =
SUMX (
	VALUES ( vwPowerBiData[DivisionName] ),
	CALCULATE (
		AVERAGE ( vwPowerBiData[ValueInUSD] ),
		KEEPFILTERS(
			NOT( vwPowerBiData[IsCurrentMonth] )
		),
		KEEPFILTERS(
			vwPowerBiData[LineItemId] = 1
		)
    )
)

I'd kindly suggest you learn how to build correct models using star schemas and how DAX works. Without this knowledge, you'll be creating measures that you'll not understand and which will return incorrect values without you even noticing it. Your model is technically feasible but it's bad. Good models follow the principles of DIMENSIONAL DESIGN. And in DAX VARiables are... well, constant, so you can't change them once they've been declared.

Best

D

[mukhan169]

Thank you. I cant thank you enough. I just couldnt understand why/how it would calculate properly for 3 months but then move to different one in the last one. But again thank you.

[AlexisOlson]

It's not clear everything that's going wrong here but this line is not doing what you think it is:

sumx(VALUES(vwPowerBiData[DivisionName]),SimpleAverage)

When you define a variable, it's treated as a constant when you reference it later. To calculate it differently for different DivisionNames, you'll need to make SimpleAverage a measure instead of a constant VAR. 

[mukhan169]

I wish there was a way to "debug" the dax queries so I dont have to spent 6 hours on this thing.