Forum Discussion

villa1980's avatar
villa1980
Resolver 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 (manager), the minimum calculation says 0 where it should be average the minimum number of days by id for the manager
Eg.
ID     Number Min days

470    21

471    6
472   2

470 and 471 belong to manager 1 so average should be 13.5
472 belong to manager 2 which should be 0.5

I have a feeling it involves Minx and filters but not too sure.

Thanks

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.

     

     

     

3 Replies

  • 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
      Resolver 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]))
      )
       



  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

    Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

    Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

    Please show the expected outcome based on the sample data you provided.

    Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523