Weighed calculation from table with DAX

[JJ_NetCo]

Hi there. I have data like this in my FactTable:

Week	Team	Type	Score
32	OA	A	35
32	OA	B	72
32	OS	A	80
32	OS	B	55
33	OA	A	47
33	OA	B	56
33	OS	A	73
33	OS	B	12

I need to group this into week, team, score(no type). But I want to calculate the grouped score, so that Type A=33,33% and B=66,66%

So a weighed result something like in SQL:

SUM(when type='a' then score*0.333

 when type='a' then score*0.666)

But how to formulate this in DAX ? New measure or column?

[JJ_NetCo]

Both solutions work., thanks.

But I still face an issue. If I put the data in a chart with teams in axis and weighed score in value, it works fine when selecting one week. If I select 2 week(in slicer), it sums the score. If I choose avarage of score as value, then...yes..it's the average of week 32 and 33. I don't need average or score, I need it to recalculate the weighed score for 2 weeks.

I also have another column 'region' in my data where all values are 'O'. So if I put region/team in my axis, I face same problem if I drill up from team-level(O is average of result for OA and OS).  Average (or Sum) instead of recalculating.

Any ideas?

I have my Fact table and Dim connections set up right(one-to-many, ).    

[jdbuchanan71]

@JJ_NetCo 

How would you calculate the weighted score for 2 weeks?  Lets take OA type A for weeks 32 and 33

Week	Team	Type	Score	Weight	Score
32	OA	A	35	33.33%	11.6655
33	OA	A	47	33.33%	15.6651

What would you expect the value for this to be if you select both week 32 and 33?  (35+47) / 2  * 33.33% is the same as AVERAGE (11.6655, 15.6651)

For the the additional column, are you able to share your .pbix file for us to look at?  I'm not really clear on the problem you are seeing without knowing the layout.

[JJ_NetCo]

Hi again. I found the problem and now it's solved. 

The data I provided in the table was a much simplified version of my data. Before persenting the score, there are lots of other calculations, both in Power Bi and sql-server. The problem was that score is a percentage measure(not just SUM), so you cannot just take the average of weeks to get the right period-based result.

Anyway, your suggestions helped me solve the problem.

[jdbuchanan71 · Accepted solution]

Hello @JJ_NetCo 

You can do it with DAX like so.

Weighted = 
SUMX(
    'Table','Table'[Score] *
    IF ( 'Table'[Type] = "A",.33,.66)
)

 

[Anonymous]

Hey buddy,

i think i got a solution.

if helps you, mark me as solution, if not, let me know.