How to SUM a value based on criteria

[Calumc12]

Hello, 

I want to use DAX to help me find the average distance ran in a game of football which can be compared to training sessions. 

All of my games are assigned with a "game" tag but I can't get the DAX right 

SUM('Data Dump 2021'[TD]),  IF= 'Data Dump 2021'[Session type]= Game

Any help would be great 

[HashamNiaz]

Hi !

You can use following DAX measure to get desired output;

Avg. Run Time = CALCULATE(AVERAGE('Data Dump 2021'[TD]), FILTER('Data Dump 2021', 'Data Dump 2021'[Sessin Type] = "Game"))

Regards,

Hasham

[Calumc12]

Hi Jihwan, 

Thats been really helpful thank you, I just need an average now instead of SUM and the SUM is adding all the running up

[Jihwan_Kim]

Hi, @Calumc12 

Thank you for your feedback.

please try the below for finding the average.

new measure =
CALCULATE (
AVERAGE ( 'Data Dump 2021'[TD] ),
'Data Dump 2021'[session type] = "Game"
)

Or, please share your sample pbix file's link here, then I can try to look into it to come up with a more accurate measure.

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Calumc12]

Hi Jihwan, 

This is brilliant and has done exactly what I am looking for. The issue i am running into now..

So now I have the distance I run on a game day, I want to compare it to what i ran in training (for example) - I have this data to hand and I have worked out a percentage change between the two variables (game distance / training distance-1) 

The issue I have now is when i chnage the date to any session which isnt a game I get no data returned and therefore the % chnage is "infinty" 

Any ideas how I can stop the game distance to not be responsive to the date selection?

[HashamNiaz]

Hi !

You can create 3 measures like below;

Avg. Game Run Time = CALCULATE(AVERAGE('Data Dump 2021'[TD]), FILTER('Data Dump 2021', 'Data Dump 2021'[Sessin Type] = "Game"))

Avg. Session Run Time = CALCULATE(AVERAGE('Data Dump 2021'[TD]), FILTER('Data Dump 2021', 'Data Dump 2021'[Sessin Type] <> "Game"))

% Diff Run Time = DIVIDE(Avg. Game Run Time, Avg. Session Run Time) - 1

The first measure will get you Avg. Game Run time, while second measure will calculate Avg. Run time for all other session type, you can change the <> "Game" to whatever session type you wan to compare.

Third measure is used to calculate % Difference between Game run time vs training run time.

Regards,

Hasham

[Calumc12]

HI Hasham, 

Thanks for your reply, this is great but the problem im running into is that I can get "game run time" unless my session is on that date. 

Usually I would want to compare the game norms to a training session although these are on different days and therefore I can compare the two

[HashamNiaz]

Hi !

So basically you want to compare Game Run time with Training Run time irrespective of dates.

Or do you want to compare these 2 KPI's monthly, yearly. For each month what's the Avg. Run time for Game vs. Training session.

Regards,

Hasham

[Jihwan_Kim]

Hi, @Calumc12 

Thank you for your feedback.

What I can suggest is that try using CALCULATE function and ALL function.

If it is OK with you, please share your sample pbix file's link here, then I can try to look into it to come up with a more accurate measure.

Thanks. 

[Calumc12]

Hi Jihwan, sorry how do I send the file into here?

[Jihwan_Kim]

Hi, @Calumc12 

Please try the below for SUM measure.

new measure =
CALCULATE (
SUM ( 'Data Dump 2021'[TD] ),
'Data Dump 2021'[session type] = "Game"
)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM