Filtering on amount >=

[Tob_P]

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

[Tob_P]

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

[ChenwuZhu_Gmail]

Hi @Tob_P ,

Have your questions been resolved and can you share them?

Best regards.

[Tob_P]

@ChenwuZhu_Gmail 

I'm afraid not - still looking for a solution. Nothing flagged up in Performance Analyser and there is a direct relationship between Sales History and Target

[johnt75]

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]

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

[johnt75]

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