Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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 unitsvalue (numerator/denominator) is calculated.
This is my model:

pbistar_1-1661774574720.png

 


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 includedIt works as desired for Group2Week 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

pbistar_0-1661774334218.png

 

5 REPLIES 5
MFelix
Super User
Super User

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:

MFelix_0-1661945891909.png

PBIX file attach.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

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

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Yes, I'll try to formulate it better!

I want to find the max values for each unit for each week. 

In group 1 for units
Unit A Week 34: num=1, denom=10 (17.08)
Unit A Week 35: num=3, denom=20 (24.08)
   -(because this gives a higher value than 23.08.2022 which is num=2, denom=20)

Unit B Week 34: num=0, denom=10 (17.08)
Unit B Week 35: num=3, denom=20 (24.08)

In group 1 for the group:
Values here are from the step above
Week 34: num=1+0, denom=10+10 --> value = 1/20
Week 35: num=3+3, denom=20+20 --> value = 3/20

This means week 35 is the largest, and the displayed value should be 0.15 for group 1.
I see that your caluclation gives the same, but that's just because of my unfortunate choice of values

Did it make any sense?
Thank you so much for taking the time to look at this!









 

Anonymous
Not applicable

Btw, It's not so important what is displayed in MaxValue on unit level, as long as the value at Group level is correct 🙂

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.