Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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.
Solved! Go to 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:
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/
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/
@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:
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/
User | Count |
---|---|
141 | |
71 | |
70 | |
54 | |
53 |
User | Count |
---|---|
208 | |
95 | |
64 | |
61 | |
57 |