Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Using MAXX in a hierarchy

Hi!

So I have a problem with calculating the max values correctly on group level, and I can't wrap my head around it. Have actually spent days, and would really love some input

I have groups, that consist of several units. And in those units a value (numerator/denominator) is calculated.
This is my model:

 


The challenge is in the measure MaxValue, that is:

MaxValue =
    CALCULATE(
        MAXX(
            VALUES('DIMDATE (2)'[WeekNumber]),
            'Values'[value]))

What I want:
For each group I want to find the week that gives the highest value, and for one week only the date with highest value should be included. It works as desired for Group2. Week 34 gives the highest value in this case, so on group level the MaxValue is (4+3)/(10+10) = 0.35

But when there is more than one date in a week, it uses the average for this week instead of only including the single date with the highest value. So Group 1 shows wrong MaxValue. 

The value for Group1 should be (3+3)/(20+20)=0.15

 

Need Help

5 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    4 years ago

    Hi Anonymous,

     

    In this case you need to go to you lowest level of granularity that is the day redo your measure to:

     

    MaxValue day = 
    CALCULATE(
        MAXX(
            VALUES('DIMDATE (2)'[Date]),
            'Values'[value]))

     

    Has you can see the result is correct at the day level:

    PBIX file attach.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi!
      Thanks for your reply! 
      However, this solution will only take the values from the one single date with the highest value.
      It should use the highest value for every single unit, not only the one highest value

      • MFelix's avatar
        MFelix
        Icon for Super User rankSuper User
        4 years ago

        Hi Anonymous ,

         

        Can you please explain a little bit better how the calculation is done? Not sure if I understood how you are picking up the values.

         

        When you refer that "The value for Group1 should be (3+3)/(20+20)=0.15"

         

        Can you tell wich lines you are picking up on each of the values.