Forum Discussion

dsandip's avatar
dsandip
Frequent Visitor
3 years ago
Solved

CALCULATE function is not working

Hi,

I using below DAX:

Cureent Quarter Sales = CALCULATE([Sales Amount],
    FILTER(ALL('Calendar'), 'Calendar'[Current QTD] = 1)
)
and want to calculate values for [Current QTD] = 1 only and for other rows it should return 0, but it return same values for all the rows. Where I am wrong ?
Below is the table sample: it should show 1732.91 where [Current QTD] =1 as I use filter condition. FOr other rows it should show 0.

 

Thanks,

Sandip

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    dsandip OK, it's incorrect but what is your definition of correct? Maybe this?

    Current Quarter Sales = 
  VAR __Current = MAX( 'Calendar'[Current QTD] )
  VAR __Result = IF( __Current = 1, CALCULATE([Sales Amount], FILTER(ALL('Calendar'), 'Calendar'[Current QTD] = 1)), 0 )
RETURN
  __Result

6 Replies

  • ppm1's avatar
    ppm1
    Solution Sage
    3 years ago

    Please try adding KEEPFILTERS to your measure.

     

    Current Quarter Sales =
CALCULATE (
    [Sales Amount],
    KEEPFILTERS ( 'Calendar'[Current QTD] = 1 )
)

     

    Pat

    • dsandip's avatar
      dsandip
      Frequent Visitor
      3 years ago

      hi,

      I think KEEPFILTERS() function only show related result, leaving every row value blank as expected, but I want the summation of JAN, Feb, March 2023 in one row, so it show like below:

      Measure 3 =
      CALCULATE (
          [Sales Amount],
          KEEPFILTERS ( 'Calendar'[Current QTD] = 1 )
      )

       

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    dsandip You should be able to do this:

     

    Cureent Quarter Sales = CALCULATE([Sales Amount], 'Calendar'[Current QTD] = 1)

    or:

    Current Quarter Sales = 
  VAR __Current = MAX( 'Calendar'[Current QTD] )
  VAR __Result = IF( __Current = 1, [Sales Amount], 0 )
RETURN
  __Result

     

     

    • dsandip's avatar
      dsandip
      Frequent Visitor
      3 years ago

      Hi,

      Your first and 2nd Measure DAX is showing below result which is incorrect:

      Measure 3 =
      CALCULATE (
          [Sales Amount],
          KEEPFILTERS ( 'Calendar'[Current QTD] = 1 )
      )
      and 
      Measure 2 =
        VAR __Current = MAX( 'Calendar'[Current QTD] )
        VAR __Result = IF( __Current = 1, [Sales Amount], 0 )
      RETURN
        __Result
       
      Thanks,
      Sandip
      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        3 years ago

        dsandip OK, it's incorrect but what is your definition of correct? Maybe this?

        Current Quarter Sales = 
  VAR __Current = MAX( 'Calendar'[Current QTD] )
  VAR __Result = IF( __Current = 1, CALCULATE([Sales Amount], FILTER(ALL('Calendar'), 'Calendar'[Current QTD] = 1)), 0 )
RETURN
  __Result