How to account for missing categorization in Measures that calculate totals?

[Anonymous]

Good day, burning the midnight oil here...

I've reworked a data model that I've previously posted on. However, because this is not a simple model, there remain complexities that I find myself challenged with.

The challenge I'm encountering is that I have "missing" rows in the context of calculations that are doing SUMs over a range. So while with two different measures I can get the different results I need/want, its rather in-elegant. Specifically I'm attempting to compare "proposed" Areas to a benchmark or Target of what the Area "should be".

• Areas are proposed by defining a project.
• When a project is defined the area of the project is categorized.
• The benchmark(s) have areas in multiple different categories, but not all projects have areas in all Categories.
• This starts to present challenges when I
  • I want to calculate Running Totals
  • When I want to compare the proposed areas to the benchmark, and more specifically when I want to start calculating relative percentages, ie what percentage of the benchmark is the area that has been added.
  • I can work around the running totals issues (or have been) but the calculations are already a challenge, and it will only get worse.
• I've been able to write two different measures that address the different contexts, what I'm looking to understand is how I might combine them into a single elegant solution that will "work" in multiple contexts.
• Screen shots below, then measures.

Calculations

Proposed Projects Diagram

Area Benchmarks Diagram

Measures

Sum Gross Benchmark = SUMX('prog Benchmark Program', 'prog Benchmark Program'[Target Area Gross])

Target Gross Calculated = CALCULATE([Sum Gross Benchmark], ALLSELECTED('prog Benchmarks'[Name]), ALL('prog Space Categories'[Name]))

Program % of Cat Benchmark Prog = DIVIDE( [Sum Gross Area Proposed], [Sum Gross Benchmark])

Sum Gross Area Proposed = CALCULATE(SUM('prog Proposed Project Program'[Program Area Gross]))

Program % of Tot Benchmark Prog = DIVIDE( [Sum Gross Area Proposed], [Target Gross Calculated])

[technolog · Accepted solution]

The main challenge you're facing is that you have missing rows in the context of calculations that are doing SUMs over a range. This is a common issue when dealing with data models that have gaps in categorization. The goal is to have a measure that can elegantly handle these gaps and provide the desired calculations.

From what I understand, you're trying to compare proposed areas to a benchmark. The benchmarks have areas in multiple categories, but not all projects have areas in all categories. This is where the missing rows come into play, and it's causing issues when you're trying to calculate running totals or relative percentages.

You've already written two measures that address the different contexts, but you want to combine them into a single solution. Let's break down the measures you've provided:

Sum Gross Benchmark - This is a straightforward SUMX over the 'prog Benchmark Program' table.
Target Gross Calculated - This measure seems to be trying to get the sum of the benchmark but resetting the context of 'prog Benchmarks' and 'prog Space Categories'. This is likely where you're trying to account for the missing categories.
Program % of Cat Benchmark Prog - This divides the proposed area by the benchmark.
Sum Gross Area Proposed - This is a straightforward sum of the proposed area.
Program % of Tot Benchmark Prog - This divides the proposed area by the total benchmark.
To address the missing rows, you can use a combination of DAX functions like IF, ISBLANK, and COALESCE. The idea is to check if a value is missing (using ISBLANK) and then provide a default value or calculation if it is.

For example, when calculating the percentage of the benchmark, you can use something like:

Program % of Cat Benchmark Prog =
IF(
ISBLANK([Sum Gross Benchmark]),
0,
DIVIDE( [Sum Gross Area Proposed], [Sum Gross Benchmark])
)
This will ensure that if the benchmark is missing (blank), the percentage will default to 0.

Similarly, for the total benchmark:

Program % of Tot Benchmark Prog =
IF(
ISBLANK([Target Gross Calculated]),
0,
DIVIDE( [Sum Gross Area Proposed], [Target Gross Calculated])
)
Now, combining these into a single measure will depend on the exact context in which you want to use them. If you want a measure that can switch between these two percentages based on some condition, you can use an IF or SWITCH statement to decide which calculation to use.

[Anonymous]

and, yes my measures' home tables are the data tables, because I find it easier to mentally organize it that way. I use measure tables for measures that don't "fit" in my mental model of the data.

note that not all key fields have "ID" in the name but I'd challenge anyone to further normalize this data structure, and if you think there is a place to do so, please specifically provide examples.