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 benchma...

Need Help

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
Benchmark.pbix25 KB

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.