Forum Discussion

pcavacas's avatar
pcavacas
Helper I
6 years ago

Problem with SUMX

I have a couple a problem with a SUMX command that I cannot figure out.    First thing I have is another DAX Measure called Latest Estimate Revenue.  The details of this command are not overly impo...
pcavacas's avatar
pcavacas
Helper I
6 years ago

What I want is for it to not take any other filters based on relationships that may exist, instead I want it to calculate the Latest Estimate Revenue based solely on values from the APF Rates table.

  • stevedep's avatar
    stevedep
    Memorable Member
    6 years ago

    Probably requires some explicit summary table to ensure calculatios are done on the right level.

    • pcavacas's avatar
      pcavacas
      Helper I
      6 years ago

      I got most of it working thanks to the help that you have provided so far, just need help with one final part.  What i have now is

       

      APF =
      SumX('VF APF Rates',
      'VF APF Rates'[Rate]
      *
      // 1
      CALCULATE([Latest Estimate Revenue],
      All(),
      Filter('Sold To',
      And('Sold To'[Aor2ID] = 'VF APF Rates'[Aor2ID],
      'Sold To'[PEID] = 'VF APF Rates'[PEID]
      )
      ),
      Filter('Product', 'Product'[ProductCategoryID] = 'VF APF Rates'[ProductCategoryID]),
      Filter('Calendar', 'Calendar'[MonthSeqId] = 275)
      )
      )
       
      The problem is with the hard coded MonthSeqId at the end.  The problem here is that in the APF Rates table there is only a single date for the month.  What need to do is take that 1 day from the APF Rate table and go to the calendar and get the MonthSeqId (which is a field on the Calendar table to denote the fiscal months) and then put that value into the filter, but am having problems writing that.
      • stevedep's avatar
        stevedep
        Memorable Member
        6 years ago

        You can try to work with variables and selected value etc.