Results 1 to 4 of 4

Thread: Weighted Measure - Different Criterias
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Apr 2011
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Weighted Measure - Different Criterias

    Hello,
    I'm looking for help on creating a measure that give me an overall score based off of other score Metrics.

    I have 2 tables:
    First table provides the employee scores for the metrics being measured:
    dDate RankGroup Full Name Production% Quality% Adherence%
    Friday, June 28, 2019 Baltimore Mickey Mouse 106% 100%
    Thursday, June 27, 2019 Baltimore Mickey Mouse 111% 100%
    Monday, July 1, 2019 Baltimore Mickey Mouse 110% 100%
    Monday, July 1, 2019 Mesa Donald Duck 97% 100% 94%
    Thursday, June 27, 2019 Mesa Donald Duck 130% 100% 50%
    Friday, June 28, 2019 Mesa Donald Duck 72% 100% 94%

    Second Table provides the % weight for each of the metrics being measured above to calculate an overall score: (notice how Baltimore doesnt have and Adherence % above - They also dont Adherence in the table below because they are not measured on it).
    RankGroup Metric Scoring Weight Old/Current Metric
    Baltimore Quality 60% Current
    Baltimore POP 40% Current
    Mesa Schedule Adherence 10% Current
    Mesa Quality 50% Current
    Mesa POP 40% Current

    I'm looking for help on how to create a measure that will caculate the Overall % based on the 2 tables above so that its using the correct Scoring Weight%. There are many more offices with different variations of the %'s so I'd need this to be able to look at the RankGroup to determine the correct % to be used.

    Appreciate the help!

  2. #2
    Board Regular
    Join Date
    Feb 2016
    Location
    Upstate NY
    Posts
    59
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Weighted Measure - Different Criterias

    That appears to be a problem that fits the exact profile of Excel's "SOLVER" feature. You will find all you need to know in the help library.

    Hope this does the trick for you.

  3. #3
    New Member
    Join Date
    Apr 2011
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Weighted Measure - Different Criterias

    Hi wornhall,
    Can you elaborate? I'm not sure how excel solver can help me here.

    I'm looking to create a Power BI measure utilizing the DAX language to calculate the Overall Score % for the 2 or 3 metrics (depending on the rankgroup).

  4. #4
    Board Regular
    Join Date
    Nov 2016
    Posts
    109
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Weighted Measure - Different Criterias

    Are you happy to consider using Power Query?
    The general approach would be to un-pivot the first table then merge with the second matching on Rank Group and Metric, multiply by the weighting, re-pivot then add weighted scores for a total.

    Peter
    Last edited by peter789; Oct 16th, 2019 at 09:40 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •