Conditional Weighted Average - DAX - Help Please

[lager1001]

Need help with the DAX coding for a conditional weighted average.

See below example.

There are 4 unique accounts (but 7 line items) in CW24, each with their respective Score. If I do a simple average, it takes all scores for all line items and I get 63.86. What I need is the weighted average so it calculates each score only once for each account and provide the average by the unique number of accounts for the calendar week. The desired outcome is the "Weighted Average Column" which would be 70.39+65.45+99.96+0.00 / 4. Some of my scores are 0.00 so these have to be inclusive to the formula. Can anyone assist?

Year/CW	Account	Score	Average Score	Weighted Average
2020-CW24	ABC	70.39	63.86	58.95
2020-CW24	ABC	70.39	63.86	58.95
2020-CW24	ABC	70.39	63.86	58.95
2020-CW24	ABC	70.39	63.86	58.95
2020-CW24	DEF	65.45	63.86	58.95
2020-CW24	GHI	99.96	63.86	58.95
2020-CW24	JKL	0.00	63.86	58.95

 

[Matt Allington]

You don’t provide any information about your table structure. I will assume you have a single table and the only data is visible in your sample. You could write this
AVERAGEX(VALUES(TableName[Account]),CALCULATE(Average(TableName[Score])))

 

[lager1001]

You don’t provide any information about your table structure. I will assume you have a single table and the only data is visible in your sample. You could write this
AVERAGEX(VALUES(TableName[Account]),CALCULATE(Average(TableName[Score])))

Hi Matt,

Thanks for the reply. Yes, it is a table from power query with rows of data for each account by calendar week. When I try the formula above I do not get the correct weighted average however. Each score should be counted only once per CW for the account in the weighted average. So in the above example, even though I have 7 rows of data, I really only have 4 scores and 4 accounts that need to be averaged, but this weighted average should appear on each line item for calendar week.

 

[macfuller]

You may need to SUMMARIZE on the Account to get the total, then divide by the count of distinct accounts. The code below assumes that Account alone is sufficient for uniquely identifying the correct score, but if there are other fields that contribute to a primary key you'll need to add those to the SUMMARIZE section.

Try this to start...

[Account Sum]:= CALCULATE (
SUMX (
SUMMARIZE (
MyTable,
MyTable[Account]
),
CALCULATE (
MAX ( MyTable[Score] ) // Assuming all Score values are the same within this summary you can use MAX, MIN, AVG, whatever
)
)

[Distinct Accounts]:=DISTINCTCOUNT ( Mytable[Account] )

[Weighted Average] = DIVIDE ( [Account Sum] , [Distinct Accounts] )

 

[lager1001]

You may need to SUMMARIZE on the Account to get the total, then divide by the count of distinct accounts. The code below assumes that Account alone is sufficient for uniquely identifying the correct score, but if there are other fields that contribute to a primary key you'll need to add those to the SUMMARIZE section.

Try this to start...

[Account Sum]:= CALCULATE (
SUMX (
SUMMARIZE (
MyTable,
MyTable[Account]
),
CALCULATE (
MAX ( MyTable[Score] ) // Assuming all Score values are the same within this summary you can use MAX, MIN, AVG, whatever
)
)

[Distinct Accounts]:=DISTINCTCOUNT ( Mytable[Account] )

[Weighted Average] = DIVIDE ( [Account Sum] , [Distinct Accounts] )

I can take this route if I need to. I am trying to keep it one column, as there are 16 different scenarios where I need this weighted average. If I go this route then I am looking at adding many more columns. DAX is frustrating that way but if that's the solution then that is what I will have to use. Thank you.

 

[macfuller]

Please be more specific. What are some of the 16 other scenarios? You can have one measure that reports back on which of the 16 scenarios was triggered, for example. (Bulky, but acceptable.) That's if they're mutually exclusive of course... otherwise you'll certainly need more columns for more measures.

 

[lager1001]

Please be more specific. What are some of the 16 other scenarios? You can have one measure that reports back on which of the 16 scenarios was triggered, for example. (Bulky, but acceptable.) That's if they're mutually exclusive of course... otherwise you'll certainly need more columns for more measures.

I have these scores broken out by 4 different categories, then by 4 different time periods.

 

[lager1001]

I can take this route if I need to. I am trying to keep it one column, as there are 16 different scenarios where I need this weighted average. If I go this route then I am looking at adding many more columns. DAX is frustrating that way but if that's the solution then that is what I will have to use. Thank you.

I used the approach above but get a circular dependency error. DAX frustrates me that way. I can write this in Excel in one column without an issue but can't figure out how to go about it in DAX given the dynamics and syntax.

 

[macfuller]

Circular dependencies usually occur on calculated columns. Did you create all 3 as measures?

 

[lager1001]

Circular dependencies usually occur on calculated columns. Did you create all 3 as measures?

I entered it as measures with a calculated column as you indicated and I get the exact same results as I do when I do an Average X formula on the Scores with an AllExcept filter on the CW and the Account.

Since each CW and each account can have multiple line items, it should only be taking that score one time and dividing over the unique count of unique CW/Score lines items but applying it to all line items with that combination.

For instance if I take all accounts and all scores for CW46 my average is 99.10 but if I only take the score for CW46 1 time for each unique account in CW46 (essentially removing duplicates) my weighted average becomes 98.38.

Calendar Week	Account	Score	Average	Weighted Average by CW and Unique Account
CW46	ABCDE	100.00	99.1	98.38
CW46	ABCDE	100.00	99.1	98.38
CW46	FGHJ	99.77	99.1	98.38
CW46	FGHJ	99.77	99.1	98.38
CW46	FGHJ	99.77	99.1	98.38
CW46	FGHJ	99.77	99.1	98.38
CW46	LMNO	100.00	99.1	98.38
CW46	LMNO	100.00	99.1	98.38
CW46	LMNO	100.00	99.1	98.38
CW46	LMNO	100.00	99.1	98.38
CW46	PQRS	100.00	99.1	98.38
CW46	PQRS	100.00	99.1	98.38
CW46	PQRS	100.00	99.1	98.38
CW46	PQRS	100.00	99.1	98.38
CW46	TUVW	99.77	99.1	98.38
CW46	TUVW	99.77	99.1	98.38
CW46	TUVW	99.77	99.1	98.38
CW46	TUVW	99.77	99.1	98.38
CW46	XYZ	99.88	99.1	98.38
CW46	XYZ	99.88	99.1	98.38
CW46	XYZ	99.88	99.1	98.38
CW46	XYZ	99.88	99.1	98.38
CW46	A123	99.88	99.1	98.38
CW46	A123	99.88	99.1	98.38
CW46	A123	99.88	99.1	98.38
CW46	A123	99.88	99.1	98.38
CW46	B234	100.00	99.1	98.38
CW46	B234	100.00	99.1	98.38
CW46	B234	100.00	99.1	98.38
CW46	B234	100.00	99.1	98.38
CW46	C123	99.97	99.1	98.38
CW46	C123	99.97	99.1	98.38
CW46	C123	99.97	99.1	98.38
CW46	C123	99.97	99.1	98.38
CW46	D234	99.89	99.1	98.38
CW46	D234	99.89	99.1	98.38
CW46	D234	99.89	99.1	98.38
CW46	D234	99.89	99.1	98.38
CW46	E123	99.92	99.1	98.38
CW46	E123	99.92	99.1	98.38
CW46	E123	99.92	99.1	98.38
CW46	E123	99.92	99.1	98.38
CW46	F234	100.00	99.1	98.38
CW46	F234	100.00	99.1	98.38
CW46	F234	100.00	99.1	98.38
CW46	F234	100.00	99.1	98.38
CW46	G123	99.96	99.1	98.38
CW46	G123	99.96	99.1	98.38
CW46	G123	99.96	99.1	98.38
CW46	G123	99.96	99.1	98.38
CW46	H234	100.00	99.1	98.38
CW46	H234	100.00	99.1	98.38
CW46	H234	100.00	99.1	98.38
CW46	H234	100.00	99.1	98.38
CW46	I123	100.00	99.1	98.38
CW46	I123	100.00	99.1	98.38
CW46	I123	100.00	99.1	98.38
CW46	I123	100.00	99.1	98.38
CW46	J234	100.00	99.1	98.38
CW46	K123	100.00	99.1	98.38
CW46	L234	99.78	99.1	98.38
CW46	M123	98.97	99.1	98.38
CW46	M123	98.97	99.1	98.38
CW46	M123	98.97	99.1	98.38
CW46	M123	98.97	99.1	98.38
CW46	M123	98.97	99.1	98.38
CW46	N234	99.81	99.1	98.38
CW46	N234	99.81	99.1	98.38
CW46	N234	99.81	99.1	98.38
CW46	N234	99.81	99.1	98.38
CW46	N234	99.81	99.1	98.38
CW46	AA123	100.00	99.1	98.38
CW46	O123	83.37	99.1	98.38
CW46	P234	99.54	99.1	98.38
CW46	Q123	99.71	99.1	98.38
CW46	R234	92.35	99.1	98.38
CW46	S123	99.36	99.1	98.38
CW46	S123	99.36	99.1	98.38
CW46	T234	99.83	99.1	98.38
CW46	T234	99.83	99.1	98.38
CW46	T234	99.83	99.1	98.38
CW46	T234	99.83	99.1	98.38
CW46	T234	99.83	99.1	98.38
CW46	U123	79.90	99.1	98.38
CW46	U123	79.90	99.1	98.38
CW46	V234	99.76	99.1	98.38
CW46	W123	100.00	99.1	98.38