Skip to main content
cancel
Showing results for 
Search instead 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

Reply
slewis
Helper I
Helper I

How to Sum by Month for a per person Average

slewis_1-1600289705246.png

 

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
DataZoe
Employee
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

 

DataZoe_0-1600305397111.png

 

 

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)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

View solution in original post

4 REPLIES 4
slewis
Helper I
Helper I

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

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)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

DataZoe
Employee
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

 

DataZoe_0-1600305397111.png

 

 

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)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

amitchandak
Super User
Super User

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

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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