Would like to get a grand maximum instead of a grand total

[bananagui]

Hello. Asking for help. Already have spent several days trying to figure out the solution myself but no success as my DAX knowledge is very poor at the moment and I am under time pressure. I will try to explain the issue from such angle...

I have a table with the following column names: "Team", "Sport", "Location", "Actual hours", "Budgeted hours".

By trial and error I created a measure (I need it to set the maximum range for Y-axis correctly).

The measure looks this way:

Measure = 

VAR one =
CALCULATE(MAXX(
                'Query1',
                CALCULATE(SUM('Query1'[BudgetedHours]),  'Query1'[Date] <= MAX ( 'Query1'[Date] ), 'Query1'[Date] >= MIN ( 'Query1'[Date] ), FILTER('Query1','Query1'[Location] = Query1[Location]     ))
            ))
      
VAR two =
CALCULATE(MAXX(
                'Query1',
                CALCULATE( SUM('Query1'[ActualHours]) , 'Query1'[Date] <= MAX ( 'Query1'[Date] ), 'Query1'[Date] >= MIN ( 'Query1'[Date] ), FILTER('Query1','Query1'[Location] = Query1[Location]     ))
            ))
    

VAR Result = MAXX({one,two},[Value])
RETURN Result

 The idea is to find a maximum value between Actual Hours and Budgeted Hours based on the filters (team, sport, location, date). This is a visual result.

I am kind of pleased about the numbers I see here but the problem for me is that the result of this measure is a grand total (9446) but I need to find a maximum value among all teams (3449 in this case). Could you help me to fix my code to get 3449 in the end instead of 9446? So I need to sum up all Actual and Budgeted hours for all the teams (or if no team specified then for the whole office) based on sport/location/date and choose the maximum among them (the numbers you see on the picture is exactly what I need) and then grand maximum as a final result. Hope I provided enough info to explain the problem 🙂 Thank you.

Just in case will add also a part from DAX Studio for getting the final table.

VAR __DS0FilterTable = 
		FILTER(
			KEEPFILTERS(VALUES('Query1'[Date])),
			AND('Query1'[Date] >= DATE(2024, 1, 31), 'Query1'[Date] < DATE(2024, 2, 1))
		)

	VAR __DS0Core = 
		SUMMARIZECOLUMNS(
			ROLLUPADDISSUBTOTAL('Query1'[Team], "IsGrandTotalRowTotal"),
			__DS0FilterTable,
			"Measure", 'Query1'[Measure]
		)

	VAR __DS0PrimaryWindowed = 
		TOPN(502, __DS0Core, [IsGrandTotalRowTotal], 0, 'Query1'[Team], 1)

EVALUATE
	__DS0PrimaryWindowed

ORDER BY
	[IsGrandTotalRowTotal] DESC, 'Query1'[Team]

[bananagui]

d*mn, just made the discovery....all these problems with wrong left and right scales were caused by the fact that I ticked "Values" under "Secondary y-axis"..I thought that "Values" is responsible for simply showing you the numbers on the chart but in reality it affects the way the charts are depicted and it distorts the correct position of marker line...turning "Values" off has solved the problem...wow.....I have always thought that such a basic task is not supposed to be so difficult....brrr...so much time wasted (..tnx for assistance..

[bananagui]

Thanks a lot, Ashish_Mathur, the solution you provided works for me (at least so far)...lbendlin, unfortunately I couldn't produce any positive result with the pseudo code you mentioned

Generally speaking, I can't understand why it was so difficult. The result I wanted to achieve is just to create a bar chart with two Y-axes where "Actual hours" is on the main bar and "Budgeted hours" is a marker on that bar..so this way visually you can easily see if actual hours is above/below the budgeted line....then I noticed that there is a problem with the scales on the left and right and if "Budgeted hours" is significantly higher..let's say 1400 vs 700 for actual hours, then the chart gets distorted because of wrong scales..so I understood that I need to use a function to set the max value of range for both Y-axes and I just don't understand why it turned out to be so difficult..I mean I see that both values for actual and budgeted hours are correctly shown on the chart (I use "Sum" type for aggregation), so I just need to choose the highest value and use it as max for Y axis..and intuitively I would expect something like max(actual, budgeted) to be an easy solution but as a result I was forced to follow the path mentioned in this thread spending several days on that "small" issue..I am just wondering if it could be done somehow easier oO

[lbendlin]

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

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

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/1447...

[Ashish_Mathur · Accepted solution]

Hi,

Does this measure work?

Measure = if(hasonevalue(Data[Team]),[Measure],MAXX(VALUES(Data[Team]),[Measure]))

[lbendlin]

You need to materialize the intermediate results by team. Pseudo code:

MAXX(

SUMMARIZECOLUMNS(Team, "m",max(budgeted, actual),

[m]

)