Filter a virtual table based on row context in a measure

[Anonymous]

I’m trying to compare the value of a measure in the context of a table visual to an AVERAGEX of the same measure, the rows iterated by the AVERAGEX needs to be filtered by the context of the row which has the value being compared. I got the basic measure working following the pattern outlined here: https://www.sqlbi.com/articles/highlighting-the-minimum-and-maximum-values-in-a-power-bi-matrix/?nu=....

I have a table (shown below) there are multiple entries related to a Site, some rows represent existing area on the site while other rows represent new Area to be added to the Site. Each row represents area in a different Category. The Area is grossed up as a calculated column. The Category ID refers to a related table which has a Boolean column [CareServices].

There is a measure:

[GT by Category] = CALCULATE(

    SUM(prog_SiteAll[Gross Program Area]),

    FILTER(

        ALLEXCEPT(prog_SiteAll, prog_SiteAll[Category ID]),

        prog_SiteAll[Date Program Available on Site] <= MAX(prog_SiteAll[Date Program Available on Site])// &&

            //prog_SiteAll[Category ID] <= MAX(prog_SiteAll[Category ID])               

    ),

    VALUES(prog_SiteAll[SiteID]),

    VALUES(prog_SiteAll[Category ID])

)

Which is a running total of the gross area.

There is second measure which calculates the area for each row as a Percentage of the Gross Total for each category:

[% of Program Category] = DIVIDE( SUM(prog_SiteAll[Gross Program Area]), [GT by Category])

What I’m looking to do is determine if the [% of Program Category] for a specific row is within a certain range as determined by my additional measure.

For example in plain English;

For Row ID 10,

Filter a virtual table AND ( Site ID, where [% pf Program Category] is greater than 0 and [CareServices] is TRUE ); AVERAGE X on [% of Program Category]

Currently I have the following measure:

Measure =

VAR Vals =

        CALCULATETABLE(

            ADDCOLUMNS(

                SUMMARIZE( prog_SiteAll, site_Sites[Abbreviated Name], prog_Categories[Name], prog_SiteAll[Project Name]),

                "%Prog", [% of Program Category]

                ),

            ALLSELECTED (),

            FILTER(prog_Categories, prog_Categories[CareServices] = TRUE())//,

            //FILTER(prog_SiteAll, [% of Program Category] > 0)           

        )

VAR HighVal = AVERAGEX(Vals, [%Prog]) + ([Grossup] * .1)

VAR LowVal = AVERAGEX(Vals, [%Prog]) - ([Grossup] * .1)

VAR CurrentVal = [% of Program Category]

VAR Result =

    SWITCH(

        TRUE(),

        CurrentVal <= LowVal, 1,

        CurrentVal >= HighVal, 1,

        0

    )




RETURN

    Result

The measure works in that I get a result, but the virtual table is not filtering properly. I can’t use EARLIER since it’s a Measure, and I can’t make this a Calculated Column since I’m using a running total measure to calculate the total area which is the basis of the percentage calculation. I’m not sure how to properly filter the virtual table based on the context of the row that the measure is in.

Greatly appreciate any advice or input!