Forum Discussion

benP's avatar
benP
Frequent Visitor
7 years ago

Comparing top performers with selection

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...
Need Help
benP's avatar
benP
Frequent Visitor
7 years ago

  • tex628's avatar
    tex628
    Community Champion
    7 years ago

    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

    • benP's avatar
      benP
      Frequent Visitor
      7 years ago

      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's avatar
        tex628
        Community Champion
        7 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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