Forum Discussion
Weighed calculation from table with DAX
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?
Hello JJ_NetCo
You can do it with DAX like so.
Weighted = SUMX( 'Table','Table'[Score] * IF ( 'Table'[Type] = "A",.33,.66) )
5 Replies
Hello JJ_NetCo
You can do it with DAX like so.
Weighted = SUMX( 'Table','Table'[Score] * IF ( 'Table'[Type] = "A",.33,.66) )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, ).
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.
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.
