Struggling with Averaging on a Filtered Measure

[Anonymous]

I'm my title doesn't do my issue service, so thanks for looking.

I have two measures that together calculates a percent value for only certain rows of values in a table. Note that the [RT Prog Area by Cat] is also a measure providing the running total that is required for the percent calculation.

Gross Area by Care Setting Ratio = //iterates through the rows in the CareSettingRatios Table and multiplies by the Program Area for each Category, note calculations are performed in current filter context, except that Program Categories will be filtered to Indepdent Care Services categories
VAR ProgramArea = CALCULATE([RT Prog Area by Cat], FILTER('prog Proposed Project Program', 'prog Proposed Project Program'[Dependent] = "FALSE"), FILTER('prog Proposed Project Program', 'prog Proposed Project Program'[CareServices] = "TRUE"))
VAR SiteRatios = VALUES(SiteCareSettingRatios)

RETURN
SUMX(SiteCareSettingRatios, SiteCareSettingRatios[Percentage] * ProgramArea)

Programmed Area Percent of Benchmark = //calculates what percent the programmed area is of the benchmark, note will calculate in the current filter context
VAR CareSettingArea = [Gross Area by Care Setting Ratio]

RETURN
DIVIDE(CareSettingArea, 'prog Benchmark Program'[Sum Gross Benchmark], 0)

 I then want to average those four values per "ServiceName" (aka CareSetting). I wrote a measure, but it returns mathematical results that don't make sense. I'm sure I'm missing something in terms of proper filtering or context. Just not sure what, as I thought this would work... 😞

Measure = 
VAR CareSettings = VALUES('prog Care Settings')
VAR PercentBench = [Programmed Area Percent of Benchmark]

RETURN
AVERAGEX(CareSettings, PercentBench)

Datamodel is a series of star schemas where some of the tables (like CareSetting and Space Category) are shared.

[Anonymous]

I don't know what the problem is but I can tell you 3 things immediately: 1) You should never use a full table in a filter under CALCULATE, 2) Variables are STATIC once set, 3) Measures should never be qualified with their table names and columns should always be.

1) There's a golden rule of DAX programming. NEVER USE A TABLE WHEN YOU CAN USE A COLUMN.

2) This in your formula above:
SUMX(SiteCareSettingRatios, SiteCareSettingRatios[Percentage] * ProgramArea)

is the same as:
ProgramArea *
SUMX(SiteCareSettingRatios, SiteCareSettingRatios[Percentage])

Once again - variables are STATIC once set.

Best
D

[Anonymous]

@Anonymous 

Regarding this:

2) This in your formula above:
SUMX(SiteCareSettingRatios, SiteCareSettingRatios[Percentage] * ProgramArea)

is the same as:
ProgramArea *
SUMX(SiteCareSettingRatios, SiteCareSettingRatios[Percentage])

---

I tried, it works, which is contrary to my understanding of how the iterative function work(ed). I then took it a step further and reduced the SUMX to just SUM and it continued to work. So I guess the iteration is implied by virtue of the "table" that is returned in the VAR Program Area (which is a filtered Measure) and the table SiteCareSettingRatios?

[Anonymous]

In fact, SUMX( Table, Table[Field] ) is totally equivalent to SUM( Table[Field] ). The latter uses the former under the hood.

Best
D

[Anonymous]

For the benifit of anyone that comes along. I think I'm making progress. I beleive my biggest issue when writing DAX is I try to overcomplicate it by applying context in my DAX, as opposed to allowing the visuals to apply context.

Maybe that makes some sense. If I do get it to work, I'll post a final solution. Until then if you have ideas for me, or suggestions, please post.

Thanks for reading.

[Anonymous · Accepted solution]

Well, I don't know if this is the "right" way to go about doing this, but I ended up using CalculateTable, to construct a virtual table to then run the averages against.

Average Percentage of Program by Care Setting = //constructs a virtual table to define the values that need to be averaged.
VAR __Table = 
    CALCULATETABLE(
        ADDCOLUMNS(
            SUMMARIZE('prog Proposed Project Program', 'prog Care Settings'[ServiceName], 'prog Space Categories'[Name]), //we need to construct a table based on Proposed Project Program and roll it up based on Care Setting and Category. Note we don't use "Related" here since the measure is calculating based on the Care Setting Ratios.
            "% of Bench", [Programmed Area Percent of Benchmark] //measure that we want the average for
        )
    )
           

RETURN
AVERAGEX(__Table, [% of Bench]) //average the column (measure), inserted into a visual with Care Setting, will then result in an average per care setting

[Anonymous]

One more thing... "FALSE" and "TRUE" are strings. False() and True() are logical constants. Mixing these things is not the best idea.

Best
D

[Anonymous]

@Anonymous 

Fair enough on the column versus table, I'm assuming this is performance related due to the Expanded Table nature of PowerBI?

Yes, I'm aware that Variables are static in the context of the measure they're defined in, is there something in the code that suggests to you that was not how I was expecting it to behave?

I'm not aware of any place where I'm qualifying a Measure name with a Table name (in fact didn't that was even possible); regardless have watched enough YouTube to have picked up at least some of the basic formatting rules. So if you saw something that you thought was a measure, but looked like a column please advise.

On the True() / False() booleans have given me much heartburn and I'm sure its something to do with my N00b'ness. The text strings do work, though certainly I'd prefer to use straight booleans, but that seems pretty minor in the grand-scheme.

Thanks,

-R

[Anonymous]

One more thing on top of one more thing... This:

VALUES('prog Care Settings')

is CERTAINLY wrong. Please check the syntax of VALUES.

Best
D

[Anonymous]

@Anonymous 

VALUES does accept a Table Name, granted might be poor DAX writing, but the syntax isn't wrong in of itself.

That said, ironically I defined thr variable, then never used it, so somewhat irrelevant... 🙂

The measure being discussed does return the expected value, the trouble is the last part in trying to average the percentages that are being properly calculated. I need to AVG by a grouping/category, and right now I can't even revese engineer the value that is being returned, so I don't know what I've done wrong....

Still looking at the SUMX formula....