Forum Discussion

villa1980's avatar
villa1980
Icon for Resolver II rankResolver II
1 year ago
Solved

Minimum and Average BY Groups

Hi all,  Probably very simple but late in the day. I have a set of data that has a minimum calculation for number of days by id. This works fine, however, when I bring in a higher level than id ...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi All ,
    Firstly , lbendlin thank you for your solution!
    And villa1980 , According to your description you have multiple time values under your id, find the smallest time value and then average it by having an id under mannger right?
     We start with the minimum number of days and then average based on the MANAGER grouping.

    Mindays = CALCULATE(
MINX (
    FILTER ( ALL ( 'Table' ), 'Table'[ID] = MAX ( 'Table'[ID] ) ),
    'Table'[Days]
))
    Average MinDays by Manager = 
AVERAGEX(
    FILTER(ALL('Table'),'Table'[Manager]=MAX('Table'[Manager])),
    [MinDays]
)

    If you still have questions you can check out my pbix file, I hope it helps and I would be honored if I could solve your problem!

    Hope it helps!

    Best regards,
    Community Support Team_ Tom Shen

    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
1 year ago

Hi All ,
Firstly , lbendlin thank you for your solution!
And villa1980 , According to your description you have multiple time values under your id, find the smallest time value and then average it by having an id under mannger right?
 We start with the minimum number of days and then average based on the MANAGER grouping.

Mindays = CALCULATE(
MINX (
    FILTER ( ALL ( 'Table' ), 'Table'[ID] = MAX ( 'Table'[ID] ) ),
    'Table'[Days]
))
Average MinDays by Manager = 
AVERAGEX(
    FILTER(ALL('Table'),'Table'[Manager]=MAX('Table'[Manager])),
    [MinDays]
)

If you still have questions you can check out my pbix file, I hope it helps and I would be honored if I could solve your problem!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

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

 

 

 

  • villa1980's avatar
    villa1980
    Icon for Resolver II rankResolver II
    1 year ago

    Thank-you for your reply, unfortunately the values returned higher than expected when using my original data.
    However, when looking at quick measure and amending it slightly, this gave me the correct values

    ELAPSEDDAYS min per CENTREID =
    AVERAGEX(
        KEEPFILTERS(VALUES('AVAILABLE_DIARY_DATE'[CENTREID])),
        CALCULATE(MIN('AVAILABLE_DIARY_DATE'[ELAPSEDDAYS]))
    )