Filtering on amount =

Question

I have been working off a piece of DAX that I used previously whereby I only wanted to show sales after a certain date which worked well and wanted to adapt it so that only sales after a certain amount where shown. Here's the DAX...

Product Target Filtered Sales (MSM Amount) =

sumx (

'tblProductTargets',

VAR BonusStartAmount ='tblProductTargets'[Start Amount]

RETURN

CALCULATE(

sum('Nav_Sales History MASTER'[Amount (LCY)]),

KEEPFILTERS(

'Nav_Sales History MASTER'[Amount (LCY)] >= BonusStartAmount)))+0

To test it out, I reset the [start amount] to zero, fully expecting to see the full amount but get this result...

Is there a reason why this would work on dates, but not on amounts? I thought it would be fairly straightforward.

Thanks

johnt75 · Answer

If [Amount (LCY)] is a measure rather than a column I think you will need to use the FILTER function rather than just passing it as a parameter to CALCULATE. Without the FILTER iterator it has no row context in which to evaluate the measure

tob_p · Answer

Hi johnt75 - Thanks for coming back to me. It's a column, not a measure.&nbsp;

johnt75 · Answer

Then the problem would seem to be in other filters which may be being applied. Is there a relationship from the targets table to sales history?

A good way to see which filters are being applied to a visual is to get the DAX code generated for it from Performance Analyzer and look at that in DAX Studio. Might point you in the right direction

tob_p · Answer

johnt75Thanks for the suggestion. I'll give that a go and see if anything comes up!

chenwuzhu_gmail · Answer

Hi&nbsp;Tob_P&nbsp;,&nbsp;Have your questions been resolved and can you share them?&nbsp;Best regards.

Forum Discussion

Filtering on amount >=

6 Replies

Related content

Dax calculate total amount ignoring filter

Formatting Dollar Amounts (e.g., $1000 -> $1K)

Filtering text with > or <

Power Query Add rows of zero amount

Visual - > Card -> Data Format