Seeking for Help. thanks

oliver_wonder

New Member
Joined
Sep 25, 2018
Messages
3
Hi All,

DateMonthValue
1/1/2018Jan2
2/1/2018Jan4
3/1/2018Jan8
1/1/2018Jan1
2/1/2018Jan3
3/1/2018Jan5
1/1/2018Jan7
2/1/2018Jan8
3/1/2018Jan9

<colgroup><col width="58" span="3" style="width:43pt"> </colgroup><tbody>
</tbody>

first i want to get the sum of each date, and find the maximum value on month,can i get the answer in one forumla by using just like Max(sum[value]). thank you very much.
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,808
maybe
Code:
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Month", type text}, {"Value", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Date", "Month"}, {{"Sum", each List.Sum([Value]), type number}}),
    #"Grouped Rows1" = Table.Group(#"Grouped Rows", {"Month"}, {{"Max", each List.Max([Sum]), type number}})
in
    #"Grouped Rows1"[/SIZE]

but this is not in single line
 
Last edited:

VBA Geek

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857
You are looking to create two measures? One with the sum per each day and the other showing the max of the month? Can you show the expected results?
 

oliver_wonder

New Member
Joined
Sep 25, 2018
Messages
3
Hi VBA Geek,

I just want one measure. and the result like column "Max of month". thank you.

DateSum of dateMax of month
1/1/20181022
2/1/20181522
3/1/20182222

<colgroup><col><col><col></colgroup><tbody>
</tbody>
 

VBA Geek

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857

ADVERTISEMENT

You can create a Date table with Date, Month, Monthname and Year.
Then get rid of the Month column in your above table and create a relationship between the 2 tables. Then create a Date hierarchy including Year, Month and Date in the Calendar table, put this hierarchy in the rows section of your matrix / pivot table and then use this measure:

TotalOfTheMonth =<br><span class="Keyword" style="color:#0070FF">IF</span><span class="Parenthesis" style="color:#969696"> (</span><br><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">HASONEVALUE</span><span class="Parenthesis" style="color:#969696"> (</span> 'Calendar'[Month] <span class="Parenthesis" style="color:#969696">)</span>,<br><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">MAXX</span><span class="Parenthesis" style="color:#969696"> (</span><br><span class="indent8">        </span><span class="Keyword" style="color:#0070FF">CALCULATETABLE</span><span class="Parenthesis" style="color:#969696"> (</span><br><span class="indent8">        </span><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">ADDCOLUMNS</span><span class="Parenthesis" style="color:#969696"> (</span><br><span class="indent8">        </span><span class="indent8">        </span><span class="Keyword" style="color:#0070FF">SUMMARIZE</span><span class="Parenthesis" style="color:#969696"> (</span> Data, 'Calendar'[Date], 'Calendar'[MonthName], 'Calendar'[Year] <span class="Parenthesis" style="color:#969696">)</span>,<br><span class="indent8">        </span><span class="indent8">        </span><span class="StringLiteral" style="color:#D93124">"TotSls"</span>, <span class="Keyword" style="color:#0070FF">CALCULATE</span><span class="Parenthesis" style="color:#969696"> (</span> <span class="Keyword" style="color:#0070FF">SUM</span><span class="Parenthesis" style="color:#969696"> (</span> Data[Value] <span class="Parenthesis" style="color:#969696">)</span> <span class="Parenthesis" style="color:#969696">)</span><br><span class="indent8">        </span><span class="indent4">    </span><span class="Parenthesis" style="color:#969696">)</span>,<br><span class="indent8">        </span><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">ALL</span><span class="Parenthesis" style="color:#969696"> (</span> 'Calendar'[Date] <span class="Parenthesis" style="color:#969696">)</span><br><span class="indent8">        </span><span class="Parenthesis" style="color:#969696">)</span>,<br>        [TotSls]<br><span class="indent4">    </span><span class="Parenthesis" style="color:#969696">)</span><br><span class="Parenthesis" style="color:#969696">)</span><br>

 

Watch MrExcel Video

Forum statistics

Threads
1,109,508
Messages
5,529,272
Members
409,859
Latest member
emperorgenghiskhan
Top