Measure won't sum up

[veranceftw]

Hello!

I have a measure that calculates the sum of a stat for every player. However, when I group those players as a team, instead of summig up all the measures for each player, it seems to be returning the average. How do I make it a sum?

Kind regards

[VijayP]

@veranceftw  What is the measure for 3.99 value , I think problem should be there

[az38]

@veranceftw 

how does look like your measure now?

[veranceftw]

Like this:

Pressure Final 3rd p90 = SUM(all_match_stats[pressures_att_3rd]) / (SUM(all_match_stats[minutes]) / 90.0)

[Icey]

Hi @veranceftw ,

For the value in the second table, it first calculates the sum of each teams of the two columns separately, and then divides the two sums. For example, the value 3.99 is calculated like so: 

Pressure Final 3rd p90 Manchester City =
CALCULATE (
    SUM ( all_match_stats[pressures_att_3rd] ),
    all_match_stats[team_name] = "Manchester City"
)
    / (
        CALCULATE (
            SUM ( all_match_stats[minutes] ),
            all_match_stats[team_name] = "Manchester City"
        ) / 90.0
    )

It is suggested to create another measure to get sum value:

Measure = SUMX('all_match_stats',[Pressure Final 3rd p90])

I create a simple example, please check the attached .pbix file.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[veranceftw]

That measure is returning NaN for me 

[Icey]

Hi @veranceftw ,

If the measure I provided doesn't work, please share me your .pbix file to help you better. Please remove sensitive information and unnecessary visuals. It is suggested to upload your file to OneDrive for Business and then paste the link here.

Best Regards,

Icey

[veranceftw]

@Icey , thanks for your help, much appreciated 🙂

Here is the pbix file! Thank you

Kind regards,

Luís

[Icey · Accepted solution]

Hi @veranceftw ,

Sorry to reply late. 

Please try to create a measure like so:

Measure 2 = SUMX(VALUES(all_match_stats[player_name]),[Pressure Final 3rd p90])

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[az38]

@veranceftw 

maybe you need smth with SUMX()

Pressure Final 3rd p90 = 
SUMX(all_match_stats,
SUM(all_match_stats[pressures_att_3rd]) / (SUM(all_match_stats[minutes]) / 90.0)
)

but more correct and appropriate answer is possible if you provide more detail about your data model

[veranceftw]

That did not work, @az38 

Well my model is very simple.

I have a dataset where each row corresponds to a match. Every match has the player_ref, team_ref and the stat. 

Since a group of players share the same team, I am using the team_ref as a proxy to group the players. However, when I do that like the image I posted it returns the average instead of the sum of all the players.

Thank you,

Luís