max in dax for values area

[LearnMeExcel]

Hi
I have in row area in my pivottable years from 2000 to 2014
In Values area I have measure the closing balance for each year .
I want to add new measure in Values area to give me the maximum year from closing . And put blank in other year

 

[Kazlik]

While I am sure there are simplier ways to do this but it works. Example below is for month totals, I made a few calcualted columns to get the month with the max total and then used that in a measure with a filter where month = max month


Month Month Sales Total Max Only

1 700
2 300
3 1100
4 1500 1500


Calcualted Columns
Month total
=CALCULATE(SUM([Amount]),FILTER(Sheet1,Sheet1[Month] = EARLIER(Sheet1[Month])))
Max Value
=MAX(Sheet1[Month Total])
Max Month Number
=IF(Sheet1[Month Total] = Sheet1[Max Value] , [Month],0)

Masures
Month Sales:=SUM(Sheet1[Amount])
Total Max Only:=CALCULATE([Month Sales],FILTER(Sheet1,Sheet1[Month] = Sheet1[Max Month Number]))

 

[LearnMeExcel]

Kazlik
than you for your help
your way work only if i don't use slicer
if i select another year from slicr the maxmium month doesn't change

i make sample data here
[TABLE="width: 143"]
<colgroup><col width="51" style="width: 38pt; mso-width-source: userset; mso-width-alt: 1865;"> <col width="65" style="width: 49pt; mso-width-source: userset; mso-width-alt: 2377;"> <col width="74" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2706;"> <tbody>[TR]
[TD="width: 51"]Year[/TD]
[TD="width: 65"]Month[/TD]
[TD="width: 74"]Sales[/TD]
[/TR]
[TR]
[TD="align: right"]2010[/TD]
[TD="align: right"]1[/TD]
[TD] 6,807,039 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2010[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"] 9,775,695 [/TD]
[/TR]
[TR]
[TD="align: right"]2010[/TD]
[TD="align: right"]3[/TD]
[TD] 9,982,864 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2010[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent"] 834,658 [/TD]
[/TR]
[TR]
[TD="align: right"]2010[/TD]
[TD="align: right"]5[/TD]
[TD] 5,026,898 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2010[/TD]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent"] 1,060,560 [/TD]
[/TR]
[TR]
[TD="align: right"]2010[/TD]
[TD="align: right"]7[/TD]
[TD] 8,970,977 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2010[/TD]
[TD="bgcolor: transparent, align: right"]8[/TD]
[TD="bgcolor: transparent"] 1,727,497 [/TD]
[/TR]
[TR]
[TD="align: right"]2010[/TD]
[TD="align: right"]9[/TD]
[TD] 313,260 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2010[/TD]
[TD="bgcolor: transparent, align: right"]10[/TD]
[TD="bgcolor: transparent"] 8,549,792 [/TD]
[/TR]
[TR]
[TD="align: right"]2010[/TD]
[TD="align: right"]11[/TD]
[TD] 880,182 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2010[/TD]
[TD="bgcolor: transparent, align: right"]12[/TD]
[TD="bgcolor: transparent"] 7,659,786 [/TD]
[/TR]
[TR]
[TD="align: right"]2011[/TD]
[TD="align: right"]1[/TD]
[TD] 928,556 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2011[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"] 9,674,966 [/TD]
[/TR]
[TR]
[TD="align: right"]2011[/TD]
[TD="align: right"]3[/TD]
[TD] 9,014,160 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2011[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent"] 5,616,727 [/TD]
[/TR]
[TR]
[TD="align: right"]2011[/TD]
[TD="align: right"]5[/TD]
[TD] 7,457,469 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2011[/TD]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent"] 9,799,451 [/TD]
[/TR]
[TR]
[TD="align: right"]2011[/TD]
[TD="align: right"]7[/TD]
[TD] 8,058,620 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2011[/TD]
[TD="bgcolor: transparent, align: right"]8[/TD]
[TD="bgcolor: transparent"] 5,069,354 [/TD]
[/TR]
[TR]
[TD="align: right"]2011[/TD]
[TD="align: right"]9[/TD]
[TD] 9,814,394 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2011[/TD]
[TD="bgcolor: transparent, align: right"]10[/TD]
[TD="bgcolor: transparent"] 6,631,784 [/TD]
[/TR]
[TR]
[TD="align: right"]2011[/TD]
[TD="align: right"]11[/TD]
[TD] 4,355,254 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2011[/TD]
[TD="bgcolor: transparent, align: right"]12[/TD]
[TD="bgcolor: transparent"] 9,031,598 [/TD]
[/TR]
[TR]
[TD="align: right"]2012[/TD]
[TD="align: right"]1[/TD]
[TD] 4,516,196 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2012[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"] 5,761,589 [/TD]
[/TR]
[TR]
[TD="align: right"]2012[/TD]
[TD="align: right"]3[/TD]
[TD] 5,553,105 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2012[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent"] 3,761,657 [/TD]
[/TR]
[TR]
[TD="align: right"]2012[/TD]
[TD="align: right"]5[/TD]
[TD] 2,000,693 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2012[/TD]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent"] 2,174,616 [/TD]
[/TR]
[TR]
[TD="align: right"]2012[/TD]
[TD="align: right"]7[/TD]
[TD] 4,044,175 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2012[/TD]
[TD="bgcolor: transparent, align: right"]8[/TD]
[TD="bgcolor: transparent"] 2,719,671 [/TD]
[/TR]
[TR]
[TD="align: right"]2012[/TD]
[TD="align: right"]9[/TD]
[TD] 7,660,405 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2012[/TD]
[TD="bgcolor: transparent, align: right"]10[/TD]
[TD="bgcolor: transparent"] 9,107,351 [/TD]
[/TR]
[TR]
[TD="align: right"]2012[/TD]
[TD="align: right"]11[/TD]
[TD] 1,042,729 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2012[/TD]
[TD="bgcolor: transparent, align: right"]12[/TD]
[TD="bgcolor: transparent"] 734,114 [/TD]
[/TR]
[TR]
[TD="align: right"]2013[/TD]
[TD="align: right"]1[/TD]
[TD] 5,560,800 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2013[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"] 8,821,386 [/TD]
[/TR]
[TR]
[TD="align: right"]2013[/TD]
[TD="align: right"]3[/TD]
[TD] 7,178,885 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2013[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent"] 5,995,635 [/TD]
[/TR]
[TR]
[TD="align: right"]2013[/TD]
[TD="align: right"]5[/TD]
[TD] 5,736,628 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2013[/TD]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent"] 2,778,949 [/TD]
[/TR]
[TR]
[TD="align: right"]2013[/TD]
[TD="align: right"]7[/TD]
[TD] 1,853,831 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2013[/TD]
[TD="bgcolor: transparent, align: right"]8[/TD]
[TD="bgcolor: transparent"] 2,982,213 [/TD]
[/TR]
[TR]
[TD="align: right"]2013[/TD]
[TD="align: right"]9[/TD]
[TD] 1,710,880 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2013[/TD]
[TD="bgcolor: transparent, align: right"]10[/TD]
[TD="bgcolor: transparent"] 9,537,009 [/TD]
[/TR]
[TR]
[TD="align: right"]2013[/TD]
[TD="align: right"]11[/TD]
[TD] 4,654,480 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2013[/TD]
[TD="bgcolor: transparent, align: right"]12[/TD]
[TD="bgcolor: transparent"] 2,222,256 [/TD]
[/TR]
[TR]
[TD="align: right"]2013[/TD]
[TD="align: right"]1[/TD]
[TD] 7,905,759 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2013[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"] 4,599,453 [/TD]
[/TR]
[TR]
[TD="align: right"]2013[/TD]
[TD="align: right"]3[/TD]
[TD] 1,607,495 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2013[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent"] 8,432,738 [/TD]
[/TR]
[TR]
[TD="align: right"]2013[/TD]
[TD="align: right"]5[/TD]
[TD] 3,551,566 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2013[/TD]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent"] 2,502,886 [/TD]
[/TR]
[TR]
[TD="align: right"]2013[/TD]
[TD="align: right"]7[/TD]
[TD] 2,604,003 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2013[/TD]
[TD="bgcolor: transparent, align: right"]8[/TD]
[TD="bgcolor: transparent"] 2,519,364 [/TD]
[/TR]
[TR]
[TD="align: right"]2013[/TD]
[TD="align: right"]9[/TD]
[TD] 7,258,019 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2013[/TD]
[TD="bgcolor: transparent, align: right"]10[/TD]
[TD="bgcolor: transparent"] 1,670,325 [/TD]
[/TR]
[TR]
[TD="align: right"]2013[/TD]
[TD="align: right"]11[/TD]
[TD] 1,934,660 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2013[/TD]
[TD="bgcolor: transparent, align: right"]12[/TD]
[TD="bgcolor: transparent"] 8,094,743 [/TD]
[/TR]
</tbody>[/TABLE]

thanks

 

[Kazlik]

Are you looking for something like this?

[TABLE="width: 255"]
<colgroup><col width="92" style="width: 69pt; mso-width-source: userset; mso-width-alt: 3364;"> <col width="65" style="width: 49pt; mso-width-source: userset; mso-width-alt: 2377;"> <col width="104" style="width: 78pt; mso-width-source: userset; mso-width-alt: 3803;"> <col width="79" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2889;"> <tbody>[TR]
[TD="width: 92"]Year[/TD]
[TD="width: 65"]Month[/TD]
[TD="width: 104"]Closing balance[/TD]
[TD="width: 79"]Max Month[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2010[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]6807039[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2010[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]9775695[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2010[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]9982864[/TD]
[TD="bgcolor: transparent, align: right"]9982864[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2010[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent, align: right"]834658[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2010[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]5026898[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2010[/TD]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent, align: right"]1060560[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2010[/TD]
[TD="bgcolor: transparent, align: right"]7[/TD]
[TD="bgcolor: transparent, align: right"]8970977[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2010[/TD]
[TD="bgcolor: transparent, align: right"]8[/TD]
[TD="bgcolor: transparent, align: right"]1727497[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2010[/TD]
[TD="bgcolor: transparent, align: right"]9[/TD]
[TD="bgcolor: transparent, align: right"]313260[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2010[/TD]
[TD="bgcolor: transparent, align: right"]10[/TD]
[TD="bgcolor: transparent, align: right"]8549792[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2010[/TD]
[TD="bgcolor: transparent, align: right"]11[/TD]
[TD="bgcolor: transparent, align: right"]880182[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2010[/TD]
[TD="bgcolor: transparent, align: right"]12[/TD]
[TD="bgcolor: transparent, align: right"]7659786[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

 

[LearnMeExcel]

yes but in my report year in slicer
when i select 2011
the MAX is sep
[TABLE="width: 133"]
<colgroup><col width="92" style="width: 69pt; mso-width-source: userset; mso-width-alt: 3364;"> <col width="85" style="width: 64pt; mso-width-source: userset; mso-width-alt: 3108;"> <tbody>[TR]
[TD="width: 92"]Row Labels[/TD]
[TD="width: 85"]Sum of Sales[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"] 928,556 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"] 9,674,966 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"] 9,014,160 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"] 5,616,727 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"] 7,457,469 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]6[/TD]
[TD="bgcolor: transparent"] 9,799,451 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]7[/TD]
[TD="bgcolor: transparent"] 8,058,620 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]8[/TD]
[TD="bgcolor: transparent"] 5,069,354 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]9[/TD]
[TD] 9,814,394 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]10[/TD]
[TD="bgcolor: transparent"] 6,631,784 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]11[/TD]
[TD="bgcolor: transparent"] 4,355,254 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]12[/TD]
[TD="bgcolor: transparent"] 9,031,598 [/TD]
[/TR]
</tbody>[/TABLE]

 

[Kazlik]

This should work where you add Year as a slicer and Year and then Month as your rows. I am sure there are other ways as well as I am new and also learning all the ins and outs of Power Pivot.


Calculated columns
Max Total
=CALCULATE(MAX(Sheet1[Sales]),FILTER(Sheet1,Sheet1[Year]=EARLIER(Sheet1[Year])))

Measures (add as values)
Closing balance:=SUM(Sheet1[Sales])
Max Month:=CALCULATE([Closing balance],FILTER(Sheet1,Sheet1[Sales] = Sheet1[Max Total]))

 

[LearnMeExcel]

thank you
it i woring just for 2010,2011 and 2012
but with 2013 it doesn't
see this result
[TABLE="width: 218"]
<colgroup><col width="92" style="width: 69pt; mso-width-source: userset; mso-width-alt: 3364;"> <col width="85" style="width: 64pt; mso-width-source: userset; mso-width-alt: 3108;"> <col width="113" style="width: 85pt; mso-width-source: userset; mso-width-alt: 4132;"> <tbody>[TR]
[TD="width: 92"]Row Labels[/TD]
[TD="width: 85"]Sum of Sales[/TD]
[TD="width: 113"]Max 2 For Month[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"] 13,466,559 [/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"] 13,420,839 [/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"] 8,786,380 [/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]4[/TD]
[TD] 14,428,373 [/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"] 9,288,194 [/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]6[/TD]
[TD="bgcolor: transparent"] 5,281,835 [/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]7[/TD]
[TD="bgcolor: transparent"] 4,457,834 [/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]8[/TD]
[TD="bgcolor: transparent"] 5,501,577 [/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]9[/TD]
[TD="bgcolor: transparent"] 8,968,899 [/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]10[/TD]
[TD="bgcolor: transparent"] 11,207,334 [/TD]
[TD="bgcolor: transparent, align: right"]9,537,009[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]11[/TD]
[TD="bgcolor: transparent"] 6,589,140 [/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]12[/TD]
[TD="bgcolor: transparent"] 10,316,999 [/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]