Forum Discussion

JJ_NetCo's avatar
JJ_NetCo
Frequent Visitor
7 years ago
Solved

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 ...
Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    7 years ago

    Hello JJ_NetCo 

    You can do it with DAX like so.

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

     

jdbuchanan71's avatar
jdbuchanan71
Super User
7 years ago

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's avatar
JJ_NetCo
Frequent Visitor
7 years ago

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.