Calculated column used for benchmarking ignoring filter/slicer

[ChristofferSN]

Hey all! 

I want to calculate a column I can use for benchmarking. The column should be untouched by my Branch filter if a branch is chosen in a slicer.

In my report I'm showing the distribution of already calculated categorical column 'Filters'[NpsType] (see picture):

If a branch manger then want to see the distribution for his branch he can use a slicer, but then I want to use a second column for benchmarking, and add to the visualization, but if he does not choose to look at any branches it should be blank. I have tried to calculate a new column with following function: 

NPSType_BM =
IF (
ISFILTERED ( 'Filters'[Branchname] ),
ALL ( 'Filters'[NpsType] ),
BLANK ()
)

Thx in advance,

Best Christoffer

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[sturlaws]

Hi, @ChristofferSN,

why do you want a column, could you do it by a measure like this:

MeasureBenchmark =
IF (
    ISFILTERED ( 'Table'[Branch] ),
    CALCULATE ( AVERAGE ( 'Table'[ValueCol] ), ALL ( 'Table'[Branch] ) ),
    BLANK ()
)

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

How to Get Your Question Answered Quickly

[ChristofferSN]

Thanks for the reply @sturlaws,

It don't necessarily have to be a new calculated column, if a measure can do the trick.

Nonetheless, in your formula you are using average, but the column 'Table' [value column] is a categorical TEXT column, and therefor it doesn't work. Which function should I use instead?

[sturlaws · Accepted solution]

If you provide example data or an example file, it is much easier to help. Here is a good guide on how to ask for help: How to Get Your Question Answered Quickly

I have created this model:

Then create these measures

Scores =
VAR _type =
    SELECTEDVALUE ( NPS[NPS] )
RETURN
    DIVIDE (
        SWITCH (
            TRUE (),
            _type = "promoters",
                CALCULATE (
                    COUNT ( 'ScoreTable'[id] ),
                    FILTER ( 'ScoreTable', 'ScoreTable'[score] >= 8 )
                ),
            _type = "detractors",
                CALCULATE (
                    COUNT ( 'ScoreTable'[id] ),
                    FILTER ( 'ScoreTable', 'ScoreTable'[score] <= 6 )
                ),
            CALCULATE (
                COUNT ( 'ScoreTable'[id] ),
                FILTER ( 'ScoreTable', 'ScoreTable'[score] > 6 && 'ScoreTable'[score] < 8 )
            )
        ),
        COUNT ( 'ScoreTable'[id] )
    )

Scores all branches =
IF (
    ISFILTERED ( BranchTable ),
    CALCULATE ( [Scores], ALL ( 'BranchTable' ) ),
    BLANK ()
)

file is available in the bottom of this post

[ChristofferSN]

Thank you!  @sturlaws 

It works now

[ChristofferSN]

I have tried with 

NPSType_BM =
VAR NpsType =
    IF (
        'Wilke Filters'[NPS] > 8,
        "Promoters",
        IF ( 'Wilke Filters'[NPS] > 6, "Passives", "Detractors" )
    )
RETURN
    IF (
        ISFILTERED ( 'Wilke Filters'[afdelingsNavn] ),
        CALCULATE (
            COUNTAX ( 'Wilke Filters', NpsType ),
            ALL ( 'Wilke Filters'[NpsType] )
        ),
        BLANK ()
    )