Forum Discussion

deepakv's avatar
deepakv
Frequent Visitor
3 years ago
Solved

Based on 2 measures can the calculation work or return null?

Hi Community,

With the below table 

If any of the measure (Cost) and measure(Prior quarter values) contains nulls the Variance column should not perform the calculation, If both the measures contains values then the variance should pop up with correct totals as I am observing the wrong sum of total for each measure.

For totals to be correct i have used-

SUMX(GROUPBY(TABLE,TABLE[PRODUCT]),CALCULATE( [Prior quarter]-[Cost]))
 
Any help would be helpful.

 

Product Costprior quarter valuesVariance (C-B)
A100009000-1000
B200000-20000
D050005000
E100007000-3000
C300000-30000
G01500015000
D1500014000-1000
C300000-30000
Need Help
  • amitchandak's avatar
    amitchandak
    3 years ago

    deepakv ,

    variance = if(not(isblank([COst])) && not(isblank([prior quarter values]))  , [prior quarter values] -[Cost], blank())

     

    or

     

    variance =Sumx(values(Table[Product]), calculate(  if(not(isblank([COst])) && not(isblank([prior quarter values]))  , [prior quarter values] -[Cost], blank()))

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    deepakv ,

    variance = if(not(isblank([COst])) && not(isblank([prior quarter values]))  , [prior quarter values] -[Cost], blank())

     

    or

     

    variance =Sumx(values(Table[Product]), calculate(  if(not(isblank([COst])) && not(isblank([prior quarter values]))  , [prior quarter values] -[Cost], blank()))