cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## How to Sum by Month for a per person Average

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.

1 ACCEPTED SOLUTION
Employee

@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.

Respectfully,
Zoe Douglas (DataZoe)

See my reports and blog at https://www.datazoepowerbi.com/

4 REPLIES 4
Helper I

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

Employee

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

Respectfully,
Zoe Douglas (DataZoe)

See my reports and blog at https://www.datazoepowerbi.com/

Employee

@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.

Respectfully,
Zoe Douglas (DataZoe)

See my reports and blog at https://www.datazoepowerbi.com/

Super User

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors