Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
lddiaz
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
Zubair_Muhammad
Community Champion
Community Champion

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]
    )

Regards
Zubair

Please try my custom visuals

View solution in original post

2 REPLIES 2
Zubair_Muhammad
Community Champion
Community Champion

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]
    )

Regards
Zubair

Please try my custom visuals

@lddiaz

 

Please see Power Pivot Model in attached Excel file

 

exposure.png

 

 


Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.