How to add FILTER to this dax measure

[JordanJackson]

Hi all, 

I'm loking for some help with adding in filters to this dax measure:

Score % difference from Target =

IF(

    NOT ISBLANK([Score]),

    DIVIDE(

        [Score] - SUM('Targets'[Ajusted Target (per million)]),

        SUM('Targets'[Ajusted Target (per million)])

    )

)

I have Regions (North, South, East, West) and I need a seperate easure filtered to each of these for the project I am working on. I am sure theres a way to just add in Region = 'Northern' but I'm not sure the best way to g about it. 

Any help would be greatly appreciated!
Thanks in advance 

[Mikelytics]

Hi @JordanJackson 

first of all due to performance you should rewrite the formula like this to save caluclation time:

Score % difference from Target =
VAR __SUM = SUM('Targets'[Ajusted Target (per million)])

RETURN

IF(
    NOT ISBLANK([Score]),
    DIVIDE(
        [Score] - __SUM,
        __SUM
    )
)

Regarding the filter in general there are 100 ways for filtering.

you can filte rin the measure using CALCULATE function. But as you describe it yoi should use a slicer or a visual filter

Slicers in Power BI - Power BI | Microsoft Learn

Format filters in Power BI reports - Power BI | Microsoft Learn

Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.

[Mikelytics · Accepted solution]

@JordanJackson 

If you want to have the filter directly in the DAX measure please try 

Score % difference from Target =
VAR __SUM = SUM('Targets'[Ajusted Target (per million)])

RETURN

IF(
    NOT ISBLANK([Score]),
   CALCULATE(
       DIVIDE(
           [Score] - __SUM,
           __SUM
       ),
   TABLE[COLUMN]="Value"
   )
)

OR

Score % difference from Target =
VAR __SUM = SUM('Targets'[Ajusted Target (per million)])

RETURN

IF(
    NOT ISBLANK([Score]),
   CALCULATE(
       DIVIDE(
           [Score] - __SUM,
           __SUM
       ),
      FILTER(
         ALL(TABLE),
         TABLE[COLUMN]="Value"
      )
   )
)

Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.

[JordanJackson]

I'll change over the Dax to the more efficent one. 

I need to filter to be in the DAX as this is for having dynamic values and I havn't seen a way to add a filter to these values so having a measure for each region seems to be the necesary soloution. 

Thankyou for your help!

[Mikelytics]

@JordanJackson 

I think I made a mistake.

The measure has to be like this:

Score % difference from Target =

IF(
    NOT ISBLANK([Score]),
   CALCULATE(

       VAR __SUM = SUM('Targets'[Ajusted Target (per million)])
       RETURN

       DIVIDE(
           [Score] - __SUM,
           __SUM
       ),
   TABLE[COLUMN]="Value"
   )
)

or this

Score % difference from Target =
IF(
    NOT ISBLANK([Score]),
   CALCULATE(
       VAR __SUM = SUM('Targets'[Ajusted Target (per million)])
       RETURN

       DIVIDE(
           [Score] - __SUM,
           __SUM
       ),
      FILTER(
         ALL(TABLE),
         TABLE[COLUMN]="Value"
      )
   )
)

Can you please try and verify?

Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.

[JordanJackson]

I've used the second one from your revised ones and its all working, thankyou for your help!

[Mikelytics]

Great! Thakn you for the feedbck! 🙂