Counting number of times a number is greater/equal/smaller than the followings (within range)

[Fabio74]

Hi all,

I have a table with the following data and I'd like to calculate all the possible outcomes for each Day if all the Teams had played against each other (last 3 columns Wins, Draws and Losses in bold). The calculation would be based on the number of Goals scored (in red):

Team Day Goals Wins Draws Losses
Team01 Day1 2 4 2 3
Team02 Day1 2 4 2 3
Team03 Day1 4 9 0 0
Team04 Day1 3 7 1 1
Team05 Day1 3 7 1 1
Team06 Day1 1 2 1 6
Team07 Day1 0 0 1 8
Team08 Day1 0 0 1 8
Team09 Day1 1 2 1 6
Team10 Day1 2 4 2 3

Provided that Goals range is C2:C11, in Excel it would be "simple" (here below is the code for Wins, adapting the ">", "=", "<" for Losses and Draws of course)

=COUNTIF($C$2:$C$11;"<"&C2))

 and since this is just a sample with just one day, I have skipped the "dynamic range" part of the Goals column. But in my Power Bi report I have 38 Days and I need the calculation to be performed within the range of each of each Day.

I guess I should use CALCULATE + something (duh) but I haven't got a clue on how to manage the greater/equal/smaller than part with DAX ... 

Thanks in advance for any help! 

[Jihwan_Kim]

Hi, @Fabio74 

Please check the below picture and the sample pbix file's link down below.

All measures are in the sample pbix file.

https://www.dropbox.com/s/4w5jho5dknln7a3/fabiov2.pbix?dl=0 

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

[Fabio74]

Thank you so much for takin the time, and also for the sample file @Jihwan_Kim.  

[Ashish_Mathur]

Hi,

This calculated column formula should work

=calculate(countrows(data),filter(data,data[goals]<earlier(data[goals])))

[Fabio74]

Thank you very much @Ashish_Mathur, your solution also works. I've just started with DAX and I use only columns, but only because it makes it easier for me to "spot" faults in my logic or wrong (not as expected) results. The only difference (in terms of outcome) I can see with the measures proposed by @Jihwan_Kim and @jdbuchanan71 is that, altough the field's format is whole number, the calculated columns use blank (the field is empty) instead of 0. If I may ask, is there a reason for it? Because it seems to me (I might be wrong) that generally speaking (not in this particular case) there's a difference in using the values blank or 0 

Edit: I have just looked at @jdbuchanan71 sample file and maybe it depends on the +0 part in his code:

Wins Column = 
VAR _Goals = 'Table'[Goals]
RETURN
    CALCULATE ( 
        COUNTROWS('Table'),
        'Table'[Goals] < _Goals,
        ALLEXCEPT ( 'Table','Table'[Day] )
    ) + 0

[jdbuchanan71 · Accepted solution]

@Fabio74 

I think these measures will give you what you are looking for.

Wins = 
VAR _Goals = SELECTEDVALUE ( 'Table'[Goals] )
RETURN
    CALCULATE(
        COUNTROWS ( 'Table' ), 
        'Table'[Goals] < _Goals,
        ALLEXCEPT('Table','Table'[Day])
    ) + 0

Draws = 
VAR _Goals = SELECTEDVALUE ( 'Table'[Goals] )
RETURN
    CALCULATE(
        COUNTROWS ( 'Table' ), 
        'Table'[Goals] = _Goals,
        ALLEXCEPT('Table','Table'[Day])
    ) - 1

Losses = 
VAR _Goals = SELECTEDVALUE ( 'Table'[Goals] )
RETURN
    CALCULATE(
        COUNTROWS ( 'Table' ), 
        'Table'[Goals] > _Goals,
        ALLEXCEPT('Table','Table'[Day])
    ) + 0

The -1 on draws is so the team doesn't count itself as a draw.

I attached my sample file for you to look at.  It also has them as calculated colmns in the table, was't sure how you wanted it.  I added 2 extra days of data just to test against.

[Fabio74]

Thank you very much @jdbuchanan71. All of the solutions proposed where correct and the outcome was the one I desidered. I've accepted this as solution only because I have both measures and columns and also because the COALESCE used by @Jihwan_Kim is much too advanced for me at the moment (I've seen it using in SQL but I couldn't understand how it works - haven't had much time). Have a nice day!