topic Re: How to SUM a value based on criteria in DAX Commands and Tips

How to SUM a value based on criteria

Calumc12 — Tue, 11 May 2021 11:34:48 GMT

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

Re: How to SUM a value based on criteria

Jihwan_Kim — Tue, 11 May 2021 11:44:30 GMT

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

Re: How to SUM a value based on criteria

Calumc12 — Tue, 11 May 2021 12:24:10 GMT

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

Re: How to SUM a value based on criteria

Jihwan_Kim — Tue, 11 May 2021 12:38:12 GMT

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

Re: How to SUM a value based on criteria

HashamNiaz — Tue, 11 May 2021 13:24:58 GMT

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

Re: How to SUM a value based on criteria

Calumc12 — Tue, 11 May 2021 16:41:53 GMT

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?

Re: How to SUM a value based on criteria

Jihwan_Kim — Tue, 11 May 2021 16:52:05 GMT

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.

Re: How to SUM a value based on criteria

Calumc12 — Tue, 11 May 2021 20:26:04 GMT

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

Re: How to SUM a value based on criteria

HashamNiaz — Tue, 11 May 2021 22:08:54 GMT

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

Re: How to SUM a value based on criteria

Calumc12 — Wed, 12 May 2021 06:26:42 GMT

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

Re: How to SUM a value based on criteria

HashamNiaz — Wed, 12 May 2021 13:48:28 GMT

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