Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

benchmark vs selected value from slicer

Dear fellow PowerBI'ers

 

I would like to see a visual with a benchmark and my selected department (from a slicer). Obviously if none is selected i would like to see all departments and the benchmark in the visual.

So my data looks like this. we added an extra row with the department 'benchmark' and an empty value.

DepartmentValue
A5
B15
C30
D10
Benchmark 

 

We calculate the result in a measure as follows:

if(

department = "Benchmark", calculate(sum(Value),All(department)),

calculate(sum(value))

 

so with the result we have this:

DepartmentValueResult
A55
B1515
C3030
D1010
Benchmark 60

 

now I have a slicer with all the 'departments' (and the benchmark). I would like to have a visual where i always see the benchmark, regardless of the selected value in the slicer and (if one selected) the department.

 

so with the following case:

 

 

 

Need Help
  • PaulDBrown's avatar
    PaulDBrown
    5 years ago

    Anonymous 

     

    Here is one way of doing this.

    The initial dataset I'm building on is:

     1) In Power Query (to ensure it updates) create a new table with the unique values for department and a new row for Benchmark 

     2) Create a Department Dimension table by referencing a new table to the Department Field in your main table

    3) Load into the model, and create a relationship between the fact table and the dimension table. The Axis Table should remain unrelated to any other tables:

     

     4) Create the measures for the visual:

     

     

    Sum Values = SUM('Table'[Value])
    Benchmark Value = CALCULATE([Sum Values], 
                    ALL('DIM Department'[Department]))
    Chart Values = CALCULATE([Sum Values], 
                TREATAS(VALUES('Axis Table'[Axis Department]), 'DIM Department'[Department]))
    Chart measure = IF(SELECTEDVALUE('Axis Table'[Axis Department]) = "Benchmark", 
                        [Benchmark Value], 
                            [Chart Values])

     

     

    5) Create a measure to filter the visual based on the slicer selection:

     

     

    Filter Chart = 
VAR Dep = VALUES('DIM Department'[Department])
VAR BENCH = {"Benchmark"}
VAR NewT = UNION(Dep, Bench)
VAR CHART = VALUES('Axis Table'[Axis Department])
RETURN
COUNTROWS(
    INTERSECT(NewT, CHART))

     

     

     

    6) Build your visual using the axis table department field as the x-axis & the [chart measure]. Add the slicer from the DIM department. Finally, in the filter pane, in filters for this visual, add the [Filter Chart] measure to the Axis department, select TopN and set the value to 1:

     

    I've attached the sample file for your reference

2 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    5 years ago

    Anonymous 

     

    Here is one way of doing this.

    The initial dataset I'm building on is:

     1) In Power Query (to ensure it updates) create a new table with the unique values for department and a new row for Benchmark 

     2) Create a Department Dimension table by referencing a new table to the Department Field in your main table

    3) Load into the model, and create a relationship between the fact table and the dimension table. The Axis Table should remain unrelated to any other tables:

     

     4) Create the measures for the visual:

     

     

    Sum Values = SUM('Table'[Value])
    Benchmark Value = CALCULATE([Sum Values], 
                    ALL('DIM Department'[Department]))
    Chart Values = CALCULATE([Sum Values], 
                TREATAS(VALUES('Axis Table'[Axis Department]), 'DIM Department'[Department]))
    Chart measure = IF(SELECTEDVALUE('Axis Table'[Axis Department]) = "Benchmark", 
                        [Benchmark Value], 
                            [Chart Values])

     

     

    5) Create a measure to filter the visual based on the slicer selection:

     

     

    Filter Chart = 
VAR Dep = VALUES('DIM Department'[Department])
VAR BENCH = {"Benchmark"}
VAR NewT = UNION(Dep, Bench)
VAR CHART = VALUES('Axis Table'[Axis Department])
RETURN
COUNTROWS(
    INTERSECT(NewT, CHART))

     

     

     

    6) Build your visual using the axis table department field as the x-axis & the [chart measure]. Add the slicer from the DIM department. Finally, in the filter pane, in filters for this visual, add the [Filter Chart] measure to the Axis department, select TopN and set the value to 1:

     

    I've attached the sample file for your reference

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    5 years ago

    Hi, Anonymous 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    Department(a calculated table):

    Department = 
FILTER(
    DISTINCT('Table'[Department]),
    [Department]<>"Benchmark"
)

     

    You may create a measure as below.

    Result = 
var d = MAX('Table'[Department])
return
IF(
        d<>"Benchmark",
        IF(
            d in DISTINCT(Department[Department]),
            SUM('Table'[Value])
        ),
        CALCULATE(
            SUM('Table'[Value]),
            FILTER(
                ALL('Table'),
                [Department] in DISTINCT(Department[Department])
            )
        )
)

     

    Then you need to make the option 'Show items with no data' unchecked and use 'Department' column from 'Department' table to filter the result.

     

    Result:

     

    Best Regards

    Allan

     

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