cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lddiaz Frequent Visitor
Frequent Visitor

A rule that affects only subsequent values

Hello all, I need some help figuring out how or if Power BI is capable of doing the following:

I am creating a new column, [Exposure] that measures the difference between [Total Cost] and [Amount Billed].
For some specific projects, lets call these "GIM" projects, there is a special rule that says if we reach a total [Exposure] value of $210,000 for all "GIM" projects, then any subsequent project classified within "GIM" will have an [EXPOSURE] of 0. 

Here is an example of what I mean. 
help.PNG

This is only true for "GIM" projects, and all other non-GIM projects should be left unaffected by this rule. The problem I am having is finding a way to let the rule ONLY affect the subsequent "GIM" projects after a total of 210k is reached.

Is this possible in Power BI? Any insight or advice would be appreciated here. 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: A rule that affects only subsequent values

Hi @lddiaz

 

Try this.

 

First Add an Index Column from the Query Editor...

 

Then you might be able to use this column

 

Exposure =
VAR CumulativeExposure =
    CALCULATE (
        SUM ( Table1[Total Cost] ) - SUM ( Table1[Amount Billed] ),
        FILTER (
            Table1,
            LEFT ( Table1[Project Name], 3 ) = "GIM"
                && Table1[Index] < EARLIER ( Table1[Index] )
        )
    )
RETURN
    IF (
        LEFT ( Table1[Project Name], 3 ) = "GIM"
            && CumulativeExposure < 210,
        Table1[Total Cost] - Table1[Amount Billed]
    )
2 REPLIES 2
Super User
Super User

Re: A rule that affects only subsequent values

Hi @lddiaz

 

Try this.

 

First Add an Index Column from the Query Editor...

 

Then you might be able to use this column

 

Exposure =
VAR CumulativeExposure =
    CALCULATE (
        SUM ( Table1[Total Cost] ) - SUM ( Table1[Amount Billed] ),
        FILTER (
            Table1,
            LEFT ( Table1[Project Name], 3 ) = "GIM"
                && Table1[Index] < EARLIER ( Table1[Index] )
        )
    )
RETURN
    IF (
        LEFT ( Table1[Project Name], 3 ) = "GIM"
            && CumulativeExposure < 210,
        Table1[Total Cost] - Table1[Amount Billed]
    )
Super User
Super User

Re: A rule that affects only subsequent values

@lddiaz

 

Please see Power Pivot Model in attached Excel file

 

exposure.png