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 Dimens...
  • 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

     

     

     

Greg_Deckler's avatar
Greg_Deckler
Community Champion
8 years ago

OK, so what would that look like if it was aggregated by the "time dimension"?

ovinasca's avatar
ovinasca
Frequent Visitor
8 years ago

That's TOTAL(2) in the picture, and if you aggregate by department dimension you whould obtain TOTAL (1)

  • Vvelarde's avatar
    Vvelarde
    Community Champion
    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

     

     

     

  • ovinasca's avatar
    ovinasca
    Frequent Visitor
    8 years ago

    Hope it helps.... 

     

    Maybe it's not possible....

     

    Thanks!

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago

    Is there a reason why you cannot just have 2 measures, one for SUM and one for AVERAGE?

  • ovinasca's avatar
    ovinasca
    Frequent Visitor
    8 years ago

    Well, I could try to use a work-around presentation for the data.

     

    But anyway, using two different measures I will have the same problem working with matrix:

     

     

     

     

    The red cells are wrong.....

     

     

  • ovinasca's avatar
    ovinasca
    Frequent Visitor
    8 years ago

    Thanks Vvelarde I will try tomorrow, I think i will work for that specific example, but if I have other dimension?

     

     

    Not sure....

     

    I will tell you !!!!

     

    THANKS!

  • ovinasca's avatar
    ovinasca
    Frequent Visitor
    8 years ago

    Hi Vvelarde!! 

     

    Works only for the suggested example, I mean, working only with two dimensions but works for me!

     

    So thanks!!

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Solution is great and solves the initial problem. But can you help on the Total Sum cell as well? Current solution calculates the AVERAGE on the full table which returns 5 which doesn't make much sense. What is the solution to make the AVERAGE of total SUMs instead of AVERAGE of whole table at lowest level? The correct result should be 15