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

     

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 )