Forum Discussion

ovinasca's avatar
ovinasca
Frequent Visitor
8 years ago
Solved

Measure aggregated based on dimensions

Hi all!

 

I need to create a measure that aggregates based on dimensions, I mean, when its aggregated by the Time Dimension I need to SUM the values but when its aggregated by the Department Dimension I need to AVERAGE the values.

 

Thanks in advanced.

Regards

  • Vvelarde's avatar
    Vvelarde
    8 years ago

    ovinasca

     

    Hi, try with this Measure in Values Section

     

    Measure =
IF (
    HASONEVALUE ( Table1[TIME] );
    SUM ( Table1[FTE's] );
    AVERAGE ( Table1[FTE's] )
)

    Regards

     

    Victor

    Lima - Peru

     

     

     

16 Replies

    • ovinasca's avatar
      ovinasca
      Frequent Visitor
      8 years ago

      Sorry and thanks for the post you suggested!!!

       

      The pìcture shows what should be the final result. We have a measure called FTE's with two dimensions, WEEK and DEPARTMENT.

       

      I want the values of FTE's to be aggregated as SUM when working with WEEK (Time) Dimension and I want them to be aggregated as AVERAGE when working with DEPARTMENT Dimension.

       

      Hope if helps.

       

      Thanks!!

      Regards

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi ovinasca,

     

    Can you share a sample please? In other words, can we take the non-total values as the original data?

     

    Best Regards,

    Dale

    • ovinasca's avatar
      ovinasca
      Frequent Visitor
      8 years ago

      Yes it is!

       

      The non total values are the original data and the total values should be the aggregation output depending on the dimension. 

       

      The picture shows what would be the final result

       

      Thanks!!!