Exclude blank data from calculations

[jwarner1121]

Hello,

In the following table, I'm trying to exclude blank values from my calculation for Rate per SF so that my total Rate per SF isn't including all the job size numbers in the denominator, but only for those records where the Item total is not blank. In my example below, Estimate Code, Item Code and item total are columns, and Job Size, Item Cost Total, Rate per SF. Here are my calculations and a screen shot of my table:

Job Size = SUM(Estimates[Job Size])

Item Cost Total = SUM('TBL Items'[item total])

Rate per SF = DIVIDE([Item Cost Total], [Job Size], 0)

          

I know I can filter out the rows where the item total is blank for this table, but I need to have a card with the correct Rate per SF that doesn't include rows where there is a value for job size but no value for Item Cost Total.

Thanks!

Jennifer

[jwarner1121]

Thanks for your help!

[Greg_Deckler · Accepted solution]

@jwarner1121 Try something like:

Measure = 
  VAR __Table = 
    FILTER(
      SUMMARIZE(
        'Table',
        [Estimate Code],
        [Item Code],
        "__JobSize",[Job Size],
        "__ItemCostTotal",[Item Cost Total]
      ),
      [__JobSize] <> BLANK()
    )
  VAR __Result = DIVIDE(SUMX(__Table,[__ItemCostTotal]), SUMX(__Table,[__JobSize]),0)
RETURN
  __Result