Help with calculating and average and getting around a slicer

[Huricanebrew]

Hello, I am writing a dax function and need some help. The page itself has a slicer that filters the main table via a relationship, and I am struggling to get around this. My basic goal here is to create a measure that provides the target for a gauge visualization. The basic functionality is the target needs to be the overall average for the dataset, for a specific question, for the current fiscal year. How do I need to adjust my code below? I've got through a few iterations, most recently applying the all that doesn't seem to be working. Any assistance will be appreciated.

clearGoal =

var relQ = "My supervisor provides me with a clear understanding of my work responsibilities."

var val = calculate(

    average('seedata'[value]),

    all(seeData),

    'seeData'[Current Question Text] = relQ,

    'seeData'[Fiscal Year]=MAX('seeData'[Fiscal Year])

)

RETURN

[Anonymous]

Hi @Huricanebrew ,

Has your problem been resolved? If so, could you please mark the helpful reply as Answered? This will assist others in the community who may encounter a similar issue. Thank you.

Otherwise, please provide some raw data in your tables(exclude sensitive data) with Text format and your expected result with backend logic and special examples. It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[Jihwan_Kim]

Hi,

I am not sure how your semantic model looks like but please try using ALLSELECTED DAX function in the measure.

ALLSELECTED function (DAX) - DAX | Microsoft Learn

clearGoal =
VAR relQ = "My supervisor provides me with a clear understanding of my work responsibilities."
VAR val =
    CALCULATE (
        AVERAGE ( 'seedata'[value] ),
        ALLSELECTED ( seeData ),
        'seeData'[Current Question Text] = relQ,
        'seeData'[Fiscal Year] = MAX ( 'seeData'[Fiscal Year] )
    )
RETURN
    val

[DataNinja777]

Hi @Huricanebrew ,

To ensure your measure calculates the overall average for the specified question and fiscal year, while bypassing slicer filters that might restrict your calculation, you need to refine your DAX formula. Here's an improved version:

clearGoal =
VAR relQ = "My supervisor provides me with a clear understanding of my work responsibilities."
VAR fiscalYear = MAX('seeData'[Fiscal Year])
RETURN
    CALCULATE(
        AVERAGE('seeData'[value]),
        REMOVEFILTERS('seeData'),
        'seeData'[Current Question Text] = relQ,
        'seeData'[Fiscal Year] = fiscalYear
    )

Best regards,