Return Max value as constant based on Sum of values by Group

[P_D_G]

Hi all,

I have the following dataset (screenshot below) and I'd like to create a measure which returns a constant value. This constant should be equal to maximum value of the sums of Size column by SubCategory (SubCat4 = 130 - in this case).

I'd like to use a combined line&column chart where:

- X-axis is the Subcategory column

- bar size is Sum of Size column

- line value is the measure I'd like to create

AND I don't wanna lose the filtering options on other columns.

This is similar to the expected output I'd like to have.

I just can't get my head around this. I have the sum of size / subcategory but I cannot use the measure as a constant. Any idea how to solve this? 

Thanks,

PDG

[Ashish_Mathur]

Hi,

Try these measure

Total = sum(data[size])

Constant = maxx(all(data[subcategory]),[total])

Drag the second measure to your visual.  If it does not help, then share the link from where i can download your PBI file.

[PaulDBrown]

@P_D_G
try:

1) Sum of size = SUM(Table [Size])

2) Size by subcategory = CALCULATE([Sum of size], ALLEXCEPT(Table, Table [Subcategory]))

3) Constant = MAXX(ALL(Table [Subcategory]), [Size by subcategory]))

[P_D_G]

Thanks @PaulDBrown, almost there.

The only issue I have is that the end result is not responsive to the slicers on the page. I guess I should add Categoy, Filter1-2-3 columns to allexcept as well but if I add another allexcept expression to the 2nd calculation, then I'll end up with the total sum of the size column.

Basically I'd like to have the max of sum by SubCategory but I also want to keep the dashboard dynamic, here is an example:

Scenario-1:

I have the barchart above (showing sum of size by SubCategory) on the page and 4 slicers (Filter1, Filter2, Filter3 and Category).

When no filtering is used on the page the constant line showing the max of sums (<- this is the measure we are looking for) on the chart will display 130 as the sum of the values of Subcategory4 are the highest number out of all sums.

However,

Scenario-2:

if I choose to interact with the slicer handling Filter1 column and selecting only the values where Filter1 = "A", then the line on the chart would change to 50 as the sum of SubCategory1 is the highest. 

[PaulDBrown · Accepted solution]

@P_D_G 

Ok, I've had the chance to test this out with a sample dataset.

These measures should work:

1) Sum by Subcategory:

Sum by Subcategory =
CALCULATE (
    [Sum of Size],
    FILTER (
        ALLSELECTED ( Table ),
        Table[Sucategory] = SELECTEDVALUE ( Table[Sucategory] )
    )
)

2) Max subcategory:

MAX by Subcategory =
IF (
    ISBLANK ( [Sum by Subcategory] ),
    BLANK (),
    MAXX ( ALLSELECTED ( Table ), [Sum by Subcategory] )
)

To get this example:

[P_D_G]

Hi @PaulDBrown ,

I have a follow up question.

What if I wanted to get the following:

I have 2 charts with the same data but the one is filtered using "Filter1 - A" and the other is "Filter1 - B". (picture below)

The line is based on the previously created measure and shows the values correctly (max of sums on both charts, so basically the same value as that of the highest column), however, I'd like to set the value of the line to 5.5M on both charts (which is the highest value out of the 2 highest values on the charts 4.9 vs 5.5). Is there any way to implement this to the calculation?

I think it should be a strange combination of allselected and except. But couldn't apply the logic of this solution for example.

[PaulDBrown]

@P_D_G 

How are you discriminating the filtering on both charts?

[P_D_G]

I use the same column as a filter on the visuals selecting different values on both chart. 

[PaulDBrown]

So you have two slicers, two charts and each pair is independent using the interactions formatting option?

[P_D_G]

No, this is not exactly the case - sorry if it wasn't clear.

I completely removed the slicer from the page and used the filter pane instead where I've added the column to both of the charts as a Filter on visual level and selected different values (one on each charts).

[PaulDBrown]

@P_D_G 

I don't think it is possible if the filtering takes place in the filter pane. It may be possible if the filtering is done using slicers.

[Johanno]

@PaulDBrown Great work! I'm getting confused why it works when you split the measure in one part for SUM and one part for MAX but not if I calculate the SUM as a variable within the same measure. Is there an easy explanation? 😊

[PaulDBrown]

@Johanno 

I'm afraid I haven't got the sufficient knowledge of the intricacies of DAX to be able to offer an educated explanation to your question. Suffice it to say that I have spent so many unproductive hours trying to solve measures involving variables to find out finally that in some cases splitting the measure into "bits" works (as opposed to using variablles) that I now tend to build solutions measure by measure. It has something to do with variables being stored in the DAX engine I believe, which then impacts the final result. This I see recurrently when iteration functions are used (SUMX, AVERAGEX, RANKX etc...).

So what I now tend to do is start out by creating seperate measure for complex calculations. Once I've cracked it, I then try to simplify using variables (for the purpose of avoiding overbloating fields/measures).
I did start off going down the route of trying to code a "one measure solution" straight off; It just becomes unproductive and very frustrating when you find that breaking down the measures actually works (again, this is a reflection on my ignorance rather than a problem with DAX). If transforming a number of measures into a single measure doesn't work within "10" minutes, I go with the original solution....I don't have time to battle away against my lack of understanding of the subtleties of DAX.

PS. Just to reference a very pertinent example, I tried amalgamating the measures I used in the solution offered in this thread into a single measure using variables and it didn't work. I played around for 5 mintues; I gave up. The solution works as is. For sure "there is a way", and I will for sure be more effecient as my knowledge of DAX grows (I've been at it for almost 4 years now)

[P_D_G]

Amazing, thank you!

[Johanno]

I think I understand, one solution might be:

Create a calculated table with summarized values:

SummedTable = SUMMARIZECOLUMNS('Table'[SubCategory], "SummedValues", SUM('Table'[Value]))

Then extract the max value:

MaxValue = MAX(SummedTable[SummedValues])

But it would be nice to do it without creating a table.

[P_D_G]

Thanks again.

I'd also been thinking to use this solution and the issue was that I still need the other columns as slicers on the page. So using SUMMARIZE doesn't make too much sense here.

[Johanno]

This sums only for the selected category, but I'm not sure if that is the answer?

Sum for filtered subcategory = 
VAR CurrentSubCategory = SELECTEDVALUE('Table'[SubCategory])
VAR Result = CALCULATE(SUM('Table'[Value]), ALL('Table'), 'Table'[SubCategory] = CurrentSubCategory)
RETURN Result

[P_D_G]

Hi Johanno,

thansk for the answer.

Unfortunately this isn't the solutoin I'm looking for. I don't want to select any categories. I'd like to display all subcategory at the same time on the barchart while showing the line which has the same value as the highest bar on the chart.

Hope this makes sense.

Thanks