Conditional SUM

[DaxBoi]

I have a dataset that looks like the following. 

For each ID there is an Attribute which has a score. There is also a target 0-4 with a weighting.

I'm looking to create a visual with a differential view of the weighted target score and actual score.

My plan is to have columns to calculate these values, however I'm struggling to formulate the DAX for the columns after the ~ symbol. 

I've tried Calculate/SUMX/Filters but can't get these to work as expected. Any help is much appreciated

ID	Attribute	Score	Target	Weighting	~	Sum(Target*Weight)	Diff
1	A1	4	0	0.2	~	2.6	+1.4
1	A1	4	1	0.2	~	2.6	+1.4
1	A1	4	2	0	~	2.6	+1.4
1	A1	4	3	0	~	2.6	+1.4
1	A1	4	4	0.6	~	2.6	+1.4
1	A2	...	...	...	~	...	...

[v-rzhou-msft]

Hi @DaxBoi 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your data model and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

[Greg_Deckler]

@DaxBoi  - You said:

"For clarity, I'm looking to sum based on a number of attributes - such as ID, and Attribute. And then do a second calculation (finding the differential) based on the same attributes"

PBIX is attached under sig, Table (4)

So, 2 columns:

Sum Target Weight = 
    VAR __Table = 
        ADDCOLUMNS(
            FILTER('Table (4)',[ID]=EARLIER([ID]) && [Attribute]=EARLIER([Attribute])),
            "Product",[Target]*[Weighting]
        )
RETURN
    SUMX(__Table,[Product])


Diff = 
    VAR __Table = 
        ADDCOLUMNS(
            FILTER('Table (4)',[ID]=EARLIER([ID]) && [Attribute]=EARLIER([Attribute])),
            "Product",[Score]*[Weighting]
        )
RETURN
    SUMX(__Table,[Product]) - [Column]

[amitchandak]

@DaxBoi , As measures

new score = sumx(All(Table),[Target]*[Weighting])

or
new score = sumx(Allselected(Table),[Target]*[Weighting])
or
new score = calculate(sumx(Table,[Target]*[Weighting]),All(Table))

or

new score = calculate(sumx(Table,[Target]*[Weighting]),Allselected(Table))

diff = Sum(Table[Score]) -[new score]

As new column

new score = sumx(Table,[Target]*[Weighting]

diff = [Score] -[new score] //Both are column here

[DaxBoi]

I think I wasn't clear in my question

The aim is to have a conditional sum that looks like this. The sum would be over the 5 total rows for

ID = 1 Attribute = A1

and then another sum over the rows for

ID = 1 Attribute = A2

and so on.

ID	Attribute	Score	Target	Sum(T*W)	Diff
1	A1	4	(0-4)	2.6	+1.4
+4	rows as	above
1	A2	3	(0-4)	3.5	-0.5
+4	rows
1	A3	4	(0-4)	1	+3.0

[DaxBoi]

For clarity, I'm looking to sum based on a number of attributes - such as ID, and Attribute. And then do a second calculation (finding the differential) based on the same attributes