How to Sum by Month for a per person Average

[slewis]

I have leave types listed in rows. One row per month, per person per leave type. The calculation is based on a person's availability. The per person availability is set at the difference between the number of time in a month (173) minus the amount of vacation they spent in that month (e.g 173 - 6 days of vacation would be 167 of availability for that month).
The monthly availability should be the SUM(of the invididual's availability) - the sum of their non vacation leave types. 

E.g. in Feb, James took 4 days of vacation leave which brings him to a potential availability of 169. 

I need a way of suming the availability of all persons in a month while having 2 records per person, with each record having the same value (e.g. 173, 169 etc). I can't use average because then the month will end up as an average of all the records; which is what I don't want.

[slewis]

IsInScope was too difficult to use, and apparently, very compute-intensive. I used Summerize instead.

[DataZoe]

That's awesome @slewis ! Can you share what you did so that it may help someone else out that also has this issue?

[DataZoe · Accepted solution]

@slewis I think the issue is you want to aggregate it differently depending on the scope. You can do that by using the ISINSCOPE dax statement:

https://docs.microsoft.com/en-us/dax/isinscope-function-dax#syntax

Difference = sumx('Table','Table'[Availability]-'Table'[Total])


Difference ISINSCOPE =
switch(
    true(),
    isblank( SELECTEDVALUE( 'Table'[Month] ) ),
    sumx(
        values( 'Table'[Month] ),
        sumx(
            values( 'Table'[Person] ),
            CALCULATE( minx( values( 'Table'[Leave Type] ), [Difference] ) )
        )
    ),
    isinscope( 'Table'[Month] ),
    sumx(
        values( 'Table'[Person] ),
        CALCULATE( minx( values( 'Table'[Leave Type] ), [Difference] ) )
    ),
    ISINSCOPE( 'Table'[Person] ),
    minx( values( 'Table'[Leave Type] ), [Difference] ),
    [Difference]
)

What this does is a series of checks to and then provide a different aggregation:

1. Is there multiple months? --> then added values for each month, which is the added values for each person, which is the minimum of the leave type difference between available and total.
2. Is this in the scope of a single month? --> then added values for each person, which is the minimum of the leave type difference between available and total.
3. Is this in the scope of a single person? --> then give the minimum of the leave type difference between available and total.
4. Else give the difference between the available and total.

[amitchandak]

@slewis ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.