Max of Sum Values

[cflynn_29]

I have a table that has the following columns: School - Building Space - Facility Type - Area.

Facility Type has an Area and belongs to a Building Space. Essentially i was able to get the total area by each building space.

What i really want to do is when i filter down by Building Space is to return the Max and only the Max like so:

This is as far as i got.

Total Sum = SUM(Data[Area])

Max Value = MAXX(VALUES(Data[Building Space]),[Total Sum])

Max Value_2 = MAXX(VALUES(Data[School]),[Max Value])

Any help would be apprecaited.

Thanks

[Anonymous]

You have to iterate over all the visible values in the first column (the name of which you have not shown):

MAXX(
	ALLSELECTED( T[FirstColumn] ),
	[Total Sum]
)

Be careful, though, NOT to use this measure inside a measure that does iteration. If you do this, you'll very likely get something that's total garbage due to the way ALLSELECTED works behind the scenes. Just make the measure a top-level measure and never use it in other measures. You'll then be safe. If you want to know WHY, please read this article:

https://www.sqlbi.com/articles/the-definitive-guide-to-allselected/

Best
D

[cflynn_29]

@Anonymous So it worked similarly to what my Max Value_2 Measure did but i only want it to return the Max Value and only the Max Value regardless of whats selected. I am using that for a bullet chart so in the example above the Max Value would always stay 4200 while the other areas would adjust and be measured against it. 

Then again i just might have to do this in excel that way theres a column of total area values by building space and then take the Max of that column.

Thanks

[Anonymous]

// This will work only if there is a
// separate dimension Schools and
// it's used for slicing.

[Max Across Building Space] =
	CALCULATE(
		MAXX(
			Schools,
			[Total Sum]
		),
		VALUES( Schools[Building Space] ),
		ALL( Schools )
	)

Try this one... This will give you the max of the measure across all schools with the same building space if there's only one school visible. For many schools... you'll figure out what it does 🙂

Best

D

[cflynn_29]

@Anonymous  That was so close, its nearly there except instead of totaling all of the individual areas by building space it just selected their largest individual space. It just needs to Total them by Building Space and take its maxiumum.

In the image below its taking the largest individual space 3,800 as the Max but what it needs to do is take 400 + 3800 = 4200 and that would be the maximum returned. Then i'd be all set.

This is the meausre i used from what you showed earlier: 

Max Across Building Space = CALCULATE(MAXX(Data,[Total Sum]),VALUES(Data[Building Space]),ALL(Data[School]))

Data is the table name and school is a column name. 

[Anonymous]

[Max Across All Building Spaces] =
	CALCULATE(
		MAXX(
			VALUES( Data[Building Space] ),
			[Total Sum]
		),
		ALL( Data )
	)

Best

D

[cflynn_29]

@Anonymous No dice on this one, the last one was closer and i dont think theres a solution to this. Thank you for all of the assistance though.

[Anonymous]

There certainly IS a solution but problem is nobody (so far) can understand what you really want because what you are showing us is not representative of the task. For instance, you are always showing Building Space as "Building." I don't think this is the only entry in this attribute.

You can do everything and anything with DAX.

Please explain the calculation on some GOOD examples.

Best
D

[Greg_Deckler]

Try:

Max Value = MAXX(ALL('Date'),[Area])

[cflynn_29]

@Greg_Deckler  Unfortunately that only gave the Max value of the Facility Type and not by Building Space. So instead of getting the value of 4200 it grabbed 3800.

[Greg_Deckler]

I'm with @Anonymous on this one. Going to need to see:

1. sample data as text

2. expected output

3. logic as to how to get from point A to B

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490