Forum Discussion
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:
<£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*
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])
4 Replies
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.
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.
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])