Target % not correct when using a filter to a category

[Jitmondo]

Hi All,

this is probably very simple but have not found it online or atleast the searches I have are not right.

I have a sales target of lets say 100 and and within that 100 are categories - example cars 50 and bikes 50

I get a percentage out of 100 which is fine but when I filter down to categories the dax below will still use 100 as the denominator. I want to be able to filter to cars or bikes and the sales amount to be calculated against the target number for that category

any idea on what I can put in to enable me to filter to different categories

Target % = calculate('salestable'[salessum]/'targettable'[targetsum])

any advice would be awesome thanks

[amitchandak · Accepted solution]

@Jitmondo , try like

Target % = calculate(Divide('salestable'[salessum], calculate('targettable'[targetsum], allselected() ) ) )

or

Target % = calculate(Divide('salestable'[salessum], calculate('targettable'[targetsum], all() ) ) )

[v-xiaotang · Accepted solution]

Hi @Jitmondo 

You can try this, create the measure below

Target % =
VAR _category =
    SELECTEDVALUE ( Slicer[categories] )
VAR _salessum =
    CALCULATE (
        SUM ( salestable[sales] ),
        FILTER ( ALL ( salestable ), salestable[categories] = _category )
    )
VAR _targetsum =
    CALCULATE (
        SUM ( targettable[target] ),
        FILTER ( ALL ( targettable ), targettable[categories] = _category )
    )
RETURN
    IF (
        ISBLANK ( _category ),
        'salestable'[salessum] / 'targettable'[targetsum],
        _salessum / _targetsum
    )

result

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@Jitmondo , try like

Target % = calculate(Divide('salestable'[salessum], calculate('targettable'[targetsum], allselected() ) ) )

or

Target % = calculate(Divide('salestable'[salessum], calculate('targettable'[targetsum], all() ) ) )