Comparing top performers with selection

[benP]

Hi, 

Im hoping someone can help me.

I have some Energy consumption data which is tied to a number of (480) buildings, i'm recieving data daily and loading this to SQL database. The data shows an Energy reading every 30 minutes per building. I have an overview frontpage displaying the running totals for the buildings. From there you can drill through to a specific building to see the daily totals of the selected building (I have just over 2 months worth so far). From there there is another drill through level where you can see the usage per half hour for a specific day with comparisons to the same day last week and month etc. 

Buildings are graded A,B,C,D and what i'm trying to achieve is something that allows comparisons between a selected/filtered building against the top performers within their respective grade, ideally showing where they are placing against different percentiles (top 15) etc. 

I've been trying to configure this using the Power KPI visual but I can't seem to be able to reference a single building at the lowest end of the top 15% and also plot this comparatively with a selected building, does anyone have any experience with these kind of comparisons. Its a very simple model so far, with the consumption data joined to a building data table on branch a number key. I can attach some snaps of data if required.

Any help would be greatly appreciated. 

Thanks in advance, 

Ben 

[benP]

[tex628]

I would start by creating measures calculating the KPI values that you want to compare against. These measures should ignore all external filters and always produce the current value. 

As an example, if you want to compare against the average of buildings classified as "A",

Measure = 
Calculate( 
( [Consumtion] / [Duration] ) / [BuildingCount] , 
All( 'Buildings') , 
[Classifcation] = "A"
)

This measure should always produce the average of buildings classified "A" which means that if you put it in a table together with the normal calculation you should be able to use a slicer to filter on a specific building to compare.

/ J

[v-shex-msft]

Hi @benP 

You can replace condition to use your selection fields to instead:

Measure =
CALCULATE (
    ( [Consumtion] / [Duration] ) / [BuildingCount],
    ALL ( 'Buildings' ),
    [Classifcation] IN VALUES ( Table[Column] )
)

Regards,

Xiaoxin Sheng

[benP]

Thanks so much for you reply, 

In the measure it won't allow me to specifiy [classification] = 'A' as this is not a measure, 

Is there a way around this? 

Thanks, 

Ben 

[tex628]

I assume that classification is a column, so in that case it would be 'Table1'[Classification] = "A" instead in the filter statement. 

[tangutoori]

please provide sample data...