Forum Discussion

Aethelson's avatar
Aethelson
New Member
2 years ago
Solved

Averages for treemap

Hi,

I want to use a Treemap chart to display how much time, on average, different people spend on different activities (see sample dataset below). The sum of the average activities should always be 40 hours. However, when an activity is missing in one of the weeks, the average is done over the weeks when the activity is present instead of average for the entire period, e.g.:

The average time for Activity B and Person 1 is calculated as (12 + 6)/2 = 9, instead of (12+0+6)/3 = 6

So in total, the sum for Person 1 gives me more than 40 hours:

Activity A: (9+10+14)/3= 11

Activity B: (12 + 6)/2 = 9

Activity C: (19+30+20)/3= 23

Total = 43 hours

 

and this is how it should be:

Activity A: (9+10+14)/3= 11

Activity B: (12 +0+6)/3 = 6

Activity C: (19+30+20)/3= 23

Total = 40 hours

 

PersonActivityWeek NumberHours
Person 1Activity AWeek 19
Person 1Activity BWeek 112
Person 1Activity CWeek 119
Person 1Activity AWeek 210
Person 1Activity CWeek 230
Person 1Activity AWeek 314
Person 1Activity BWeek 36
Person 1Activity CWeek 320
Person 2Activity AWeek 15
Person 2Activity BWeek 115
Person 2Activity CWeek 120
Person 2Activity AWeek 35
Person 2Activity BWeek 35
Person 2Activity CWeek 330

 

I am using this measure but it is obviously not working: 

 

Average Hours =
VAR TotalWeeks = DISTINCTCOUNT('Merged queries'[Week Number])
VAR TotalHours =
    SUMX(
        VALUES('Merged queries'[Activity]),
        CALCULATE(SUM('Merged queries'[Hours])))

RETURN
DIVIDE(
    TotalHours,
    TotalWeeks
)
 
When I remove Activity from the Treechart I get the right amount of hours.
 

A very same problem was described in this thread but it was not answered: https://community.fabric.microsoft.com/t5/Desktop/Treemap-summarization-of-a-quick-measure/m-p/2695631#M943325 

 

Thanks in advance!

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Aethelson 

    You can try the following measure.

     

    Average Hour =
VAR a =
    CALCULATE (
        DISTINCTCOUNT ( 'Merged queries'[Activity] ),
        ALLSELECTED ( 'Merged queries' ),
        'Merged queries'[Person] IN VALUES ( 'Merged queries'[Person] )
    )
VAR b =
    CALCULATE (
        SUM ( 'Merged queries'[Hours] ),
        ALLSELECTED ( 'Merged queries' ),
        'Merged queries'[Person] IN VALUES ( 'Merged queries'[Person] ),
        'Merged queries'[Activity] IN VALUES ( 'Merged queries'[Activity] )
    )
RETURN
    DIVIDE ( b, a )

     

    Output

     

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Aethelson 

    You can try the following measure.

     

    Average Hour =
VAR a =
    CALCULATE (
        DISTINCTCOUNT ( 'Merged queries'[Activity] ),
        ALLSELECTED ( 'Merged queries' ),
        'Merged queries'[Person] IN VALUES ( 'Merged queries'[Person] )
    )
VAR b =
    CALCULATE (
        SUM ( 'Merged queries'[Hours] ),
        ALLSELECTED ( 'Merged queries' ),
        'Merged queries'[Person] IN VALUES ( 'Merged queries'[Person] ),
        'Merged queries'[Activity] IN VALUES ( 'Merged queries'[Activity] )
    )
RETURN
    DIVIDE ( b, a )

     

    Output

     

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • Aethelson's avatar
      Aethelson
      New Member
      2 years ago

      Hi Yolo Zhu
      I had to modify the formula a bit (changing distinctcount from Activity to Week number), as it still didn't return 40 hours for Person 2 but it now works like a charm! Thanks a lot, you saved me many hours.

       

      Average Hour = 
VAR a =
    CALCULATE (
        DISTINCTCOUNT ( 'Merged queries'[Week Number] ),
        ALLSELECTED ( 'Merged queries' ),
        'Merged queries'[person] IN VALUES ( 'Merged queries'[person] )
    )
VAR b =
    CALCULATE (
        SUM ( 'Merged queries'[Hours] ),
        ALLSELECTED ( 'Merged queries' ),
        'Merged queries'[name] IN VALUES ( 'Merged Queries'[Name] ),
        'Merged Queries'[activity] IN VALUES ( 'Merged Queries'[activity] )
    )
RETURN
    DIVIDE ( b, a )