CODEWITHSUNDEEP
powerbidax
Marc Pincince
Marc Pincince

Reputation: 5202

Total Only the Filtered Max Values

I’m trying to create a measure using DAX. I’m starting with data in a table like this. The data table is named 1025.

![enter image description here

What I want to do with it is to determine the MAX BUDGET_AMOUNT1 for each SUBLINE_NBR1, then sum all selected SUBLINE_NBR1's MAX values. So for this screen snip, what you see should yield a total of 6,919. That is, 1,184 + 630 + 0 + 5,105 ... the MAX of BUDGET_AMOUNT1 for SUBLINE_NBR1 31-02 + the MAX of BUDGET_AMOUNT1 for SUBLINE_NBR1 31-13 + the MAX of BUDGET_AMOUNT1 for SUBLINE_NBR1 32-00 + the MAX of BUDGET_AMOUNT1 for SUBLINE_NBR1 34-03.

I'm able to determine the MAX value of each SUBLINE_NBR1 okay. I did that with this measure: 

Budget = CALCULATE(MAXX('1025','1025'[BUDGET_AMOUNT1]),DISTINCT('1025'[SUBLINE_NBR1]))

With this measure, and no records selected in the table, I get 5,105, which is what I'd expect since 5,105 is the MAX value for BUDGET_AMOUNT1 of all SUBLINE_NBR1 records. If I select a single record, I get its value. For instance, if I select a record with 630 as the BUDGET_AMOUNT1, I get 630 as the Budget measure result, which is also expected since 630 is its MAX BUDGET_AMOUNT1 value. If I select two or more records, I get whichever one's BUDGET_AMOUNT1 MAX value is the greater. For instance, if I select one with 630 and one with 1,184, I get 1,184. Again, as expected. So far, so good.

Now, instead of returning the greater BUDGET_AMOUNT1 MAX value I want to sum all the MAX values of the selected SUBLINE_NBR1 values and return that sum. For instance, if I select the fourth and seventh rows of the table, I want to get 1,814 from the measure (1,184 + 630). If I select the first seven rows of the table, I still want to get 1,814 from the measure (still 1,184 + 630 ... the MAX for SUBLINE_NBR1 31-02 + the MAX for SUBLINE_NBR1 31-13). But I seem to be having trouble with context? I guess.

I've tried a lot of things, like the following. (I won't list them all.) But I'm still quite far from a solution.

I tried:

Budget = 
var MaxVal = SUMX('1025',CALCULATE(MAXX('1025','1025'[BUDGET_AMOUNT1]),DISTINCT('1025'[SUBLINE_NBR1])))

But it adds every line...not just the MAX values. It returns 52,571 for all records, or the total of all BUDGET_AMOUNT1 values for any selected records.

I also tried:

Budget = 
var MaxVal = CALCULATE(MAXX('1025','1025'[BUDGET_AMOUNT1]),DISTINCT('1025'[SUBLINE_NBR1]))
return SUMX('1025',MaxVal)

But I'm not exactly sure what it's doing. It is returning an exorbitantly high number value (102,100 for these 20 records). It appears to be trying to add all selected values...and more. It doesn't add them correctly when crossing SUBLINE_NBR1 groups. Regardless, this obviously isn't what I want either.

There is probably a simple solution, but, yes... I'm at a loss and could definitely use your help!

Upvotes: 0

Views: 2115

Answers (2)

Alexis Olson
Alexis Olson

Reputation: 40204

@RADO's solution is good, but here's another option to consider:

= SUMX(
      SUMMARIZE('1025',
          '1025'[SUBLINE_NBR1],
          "MaxVal", MAX('1025'[BUDGET_AMOUNT1])),
      [MaxVal])

The SUMMARIZE function creates a table grouping distinct SUBLINE_NBR1 values (much like a SQL GROUP BY) and takes the max of the BUDGET_AMOUNT1 as the aggregation calls it MaxVal. With no filters, that table is:

Summarize Output

Then the SUMX goes through each row in the above table and adds the MaxVal column.

Upvotes: 0

RADO
RADO

Reputation: 8148

Budget =
SUMX (
    VALUES ( '1025'[SUBLINE_NBR1] ),
    CALCULATE ( MAX ( '1025'[BUDGET_AMOUNT1] ) )

)

How it works: First, VALUES generates a list of unique SUBLINE_NBR1 in your context (you can also use DISTINCT instead of VALUES if you have blanks in SUBLINE_NBR1 and want to ignore them).

Second, SUMX iterates over the list of SUBLINE_NBR1, computes Max budget amount for each of them, and then sums the max values up.

Upvotes: 1

Related Questions