Occupany, Culmulative total help

[molzmad]

Hi, i basically have a column labeled occupancy 2, which is a culumaltive total of people entering or exiting a building, im wanting to understand how to modify my DAX so that the occupancy 2 cant go below zero, if anyone would be able to help it would be greatly appreciated 🙂

[amitchandak]

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

Try

a new column =

if([Occupancy] <0 , 0, [Occupancy])

[molzmad]

Hi, i have attached a sample here, i tried do the new column and it didnt work :(, thanks so much for taking a look i really appreciate it 🙂

https://drive.google.com/file/d/11ubbPcRiZDj--3osMqBtXziRB4mZ5r3m/view?usp=sharing 

[molzmad]

I basically need it to do a running total, but if the total goes below zero then to make it zero, as i dont want the occupancy levels going into minus 🙂

[v-yuaj-msft]

Hi @molzmad ,

It's not clear to me. I did a test. Is the following result what you want?

Column =
VAR x1 =
    CALCULATE (
        SUM ( 'Enliteon_Office_Test Count_Events'[Ins_Outs] ),
        FILTER (
            ALL ( 'Enliteon_Office_Test Count_Events' ),
            [Custom] <= EARLIER ( 'Enliteon_Office_Test Count_Events'[Custom] )
        )
    )
RETURN
    IF ( x1 < 0, 0, x1 )

Hope that's what you were looking for.

Best Regards,

Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[molzmad]

Hi,
Yes thats right, but how come it doesnt start counting from the beggining i.e. top row? Is there anyway to implement this? For example its counting some of the ins as 0

[v-yuaj-msft]

Hi @molzmad ,

Thanks for your reply. I think it does count from the beginning. You can sort the "timestamp" column ascending. 

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[molzmad]

Hi, 
yeah it does seem to work but i mean for example you have put above, in the second row, the value for Ins_Outs = 1, so "column" should = 1  if that makes sense ?

[v-yuaj-msft]

Hi @molzmad ,

So you want to create a new column, where the negative value is zero and the positive value remains the same, right?

Best Regards,

Yuna

[molzmad]

Yeah basically, i just want the accumulated column to count but not go below zero :),

Thanks :),
Molly

[v-yuaj-msft]

Hi @molzmad ,

Is the following result what you want? (column 2)

Best Regards,

Yuna

[molzmad]

Hi 🙂 column two looks right but i just want it to count accumulatively i.e. column 2 would look like this :

[v-yuaj-msft]

Hi @molzmad ,

Based on your description, you can create two calculated columns as follows.

Rank =
RANKX ( ALL ( 'Enliteon_Office_Test Count_Events' ), [timestamp],, ASC )

Test_value =
VAR x1 =
    MAXX (
        FILTER (
            ALL ( 'Enliteon_Office_Test Count_Events' ),
            [Rank] <= EARLIER ( 'Enliteon_Office_Test Count_Events'[Rank] )
                && [Ins_Outs] < 0
        ),
        [Rank]
    )
RETURN
    [Rank] - x1

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[molzmad]

Hi almost, i just want the -1 to minus from the prior number i.e. 3,2,1 rather than the -1 equal zero if that makes sense, but just not allow the column to go below zero, like an accumulative sum that cant go below zero