topic Re: Filter on Table Q. in Developer

Filter on Table Q.

Anonymous — Tue, 06 Nov 2018 15:05:13 GMT

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:

<£50k = SUMX ( FILTER ( 'LTM 050K', 'LTM 050K'[pc_regionno] = 'LTM Live'[pc_regionno] && 'LTM 050K'[statuscode] = 866120000 || 'LTM 050K'[statuscode] = 866120007 && 'LTM 050K'[createdon] >= 'LTM Live'[xxxBegin] && 'LTM 050K'[createdon] <= 'LTM Live'[xxxEnd] ), 'LTM 050K'[tsg_quoteprice_base])

In the SUMX(), I am not putting an amount filter because I am counting on the amount filter to hold. Is this correct?

Kindest,

Jonathan M*

Re: Filter on Table Q.

Greg_Deckler — Tue, 06 Nov 2018 16:16:48 GMT

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.

Re: Filter on Table Q.

Anonymous — Tue, 06 Nov 2018 16:22:32 GMT

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.

Re: Filter on Table Q.

Greg_Deckler — Tue, 06 Nov 2018 16:46:43 GMT

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])

Re: Filter on Table Q.

Anonymous — Tue, 06 Nov 2018 16:37:52 GMT

@Greg_Deckler you are the greatest Datanaut EVER!