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

 

[lager1001]

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

What I am needing is the get the weighted average per week, per account, not the average of all lines.

 

[macfuller]

I only gave 3 formulas, all should be measures. I didn't say anything (I think) about a calculated column.

If Calendar Week and Account are the unique identifiers for a Score (e.g. CW46 and Account A234 always have 65.2, but CW47 and Account A234 always have 84.3) then you need the 2nd parameter for the Account Sum measure.

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

 

[lager1001]

I only gave 3 formulas, all should be measures. I didn't say anything (I think) about a calculated column.

If Calendar Week and Account are the unique identifiers for a Score (e.g. CW46 and Account A234 always have 65.2, but CW47 and Account A234 always have 84.3) then you need the 2nd parameter for the Account Sum measure.

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

I need to have this as a column. The way I read your original response was to use two measures and the third was a calculated column based off of the two measures. When I applied that I got a standard average and not the weighted average I need.

 

[macfuller]

Why as a column? It's much less flexible, and you'll still be duplicating values. In any case, when you used a column it was pulling all values without filtering by your calendar week so you got a weighted average for the entire table - that's why it didn't change.

You'll have to play with the following column formula - since I don't have the data I can't tweak it the way to test it. You need to basically combine the logic from all 3 measures into a single column. I have a tough time with FILTER arguments and I'm never quite sure if I need the FILTER(ALL(...)) construct until I see the data. Anyway, try this and tweak it to see if your column gives what you need.

[Account Sum] =
VAR my_account = MyTable[Account]
VAR my_week = MyTable[Calendar Week]
VAR sum_of_items =
    CALCULATE (
        SUMX (
            SUMMARIZE ( MyTable, MyTable[Account], MyTable[Calendar Week] ),  // not sure if you need a FILTER here or if this will operate on the whole table
            CALCULATE (
                MAX ( MyTable[Score] ) // Assuming all Score values are the same within this summary you can use MAX, MIN, AVG, whatever
                ,
                MyTable[Calendar Week] = my_week // only add values for accounts in the current week
            )
        )
    )
VAR count_of_accounts =
    CALCULATE (
        DISTINCTCOUNT ( MyTable[Account] ),
        FILTER ( ALL ( MyTable[Calendar Week] ), MyTable[Calendar Week] = my_week )
    )
VAR weighted_score =
    DIVIDE ( sum_of_items, count_of_accounts )
RETURN
    weighted_score

 

[lager1001]

Why as a column? It's much less flexible, and you'll still be duplicating values. In any case, when you used a column it was pulling all values without filtering by your calendar week so you got a weighted average for the entire table - that's why it didn't change.

You'll have to play with the following column formula - since I don't have the data I can't tweak it the way to test it. You need to basically combine the logic from all 3 measures into a single column. I have a tough time with FILTER arguments and I'm never quite sure if I need the FILTER(ALL(...)) construct until I see the data. Anyway, try this and tweak it to see if your column gives what you need.

[Account Sum] =
VAR my_account = MyTable[Account]
VAR my_week = MyTable[Calendar Week]
VAR sum_of_items =
    CALCULATE (
        SUMX (
            SUMMARIZE ( MyTable, MyTable[Account], MyTable[Calendar Week] ),  // not sure if you need a FILTER here or if this will operate on the whole table
            CALCULATE (
                MAX ( MyTable[Score] ) // Assuming all Score values are the same within this summary you can use MAX, MIN, AVG, whatever
                ,
                MyTable[Calendar Week] = my_week // only add values for accounts in the current week
            )
        )
    )
VAR count_of_accounts =
    CALCULATE (
        DISTINCTCOUNT ( MyTable[Account] ),
        FILTER ( ALL ( MyTable[Calendar Week] ), MyTable[Calendar Week] = my_week )
    )
VAR weighted_score =
    DIVIDE ( sum_of_items, count_of_accounts )
RETURN
    weighted_score

I used the measures was able to get the results I needed for charting. I will refer to your above code if it is requested that these be in columns.