Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Filter on Table Q.

In a table, I placed a filter on the amount column to be 50,000 and less.  When I run SUMX() on that table, do I need to be concerned that the amount filter goes away?   my sumx looks like this:  ...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    7 years ago

    OK, I believe you want something like this. Assumption is that you have an [Amount] column.

     

    <£50k = SUMX ( FILTER ( 'LTM 050K', 'LTM 050K'[pc_regionno] = 'LTM Live'[pc_regionno] && 'LTM 050K'[Amount]<50000), 'LTM 050K'[tsg_quoteprice_base])

>£50k<=£100k = SUMX ( FILTER ( 'LTM 050K', 'LTM 050K'[pc_regionno] = 'LTM Live'[pc_regionno] && 'LTM 050K'[Amount]>50000 && 'LTM 050K'[Amount]<=100000), 'LTM 050K'[tsg_quoteprice_base])
Greg_Deckler's avatar
Greg_Deckler
Community Champion
7 years ago

If I understand you correctly and you filted in the data tab in Desktop then no, that will not "stay" you will need to include that in your SUMX. That filtering interface is just so that you can find an look at records easier.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Greg,

     

    How do I filter on a dollar amount field in a sumx()?  I need to know how to say less than 50,000 and between 50,000.01 to 100,000 and several others.

     

    <£50k = SUMX ( FILTER ( 'LTM 050K', 'LTM 050K'[pc_regionno] = 'LTM Live'[pc_regionno]), 'LTM 050K'[tsg_quoteprice_base])

     

    Any help would be great.

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      7 years ago

      OK, I believe you want something like this. Assumption is that you have an [Amount] column.

       

      <£50k = SUMX ( FILTER ( 'LTM 050K', 'LTM 050K'[pc_regionno] = 'LTM Live'[pc_regionno] && 'LTM 050K'[Amount]<50000), 'LTM 050K'[tsg_quoteprice_base])

>£50k<=£100k = SUMX ( FILTER ( 'LTM 050K', 'LTM 050K'[pc_regionno] = 'LTM Live'[pc_regionno] && 'LTM 050K'[Amount]>50000 && 'LTM 050K'[Amount]<=100000), 'LTM 050K'[tsg_quoteprice_base])
      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Greg_Deckler you are the greatest Datanaut EVER!