Forum Discussion

jwarner1121's avatar
jwarner1121
Frequent Visitor
3 years ago
Solved

Exclude blank data from calculations

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

 
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    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

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    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