Seeking for Help. thanks

[oliver_wonder]

Hi All,

[TABLE="class: outer_border, width: 174"]
<colgroup><col width="58" span="3" style="width:43pt"> </colgroup><tbody>[TR]
[TD="width: 58"]Date[/TD]
[TD="width: 58"]Month[/TD]
[TD="width: 58"]Value[/TD]
[/TR]
[TR]
[TD="align: right"]1/1/2018[/TD]
[TD]Jan[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]2/1/2018[/TD]
[TD]Jan[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]3/1/2018[/TD]
[TD]Jan[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]1/1/2018[/TD]
[TD]Jan[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2/1/2018[/TD]
[TD]Jan[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]3/1/2018[/TD]
[TD]Jan[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]1/1/2018[/TD]
[TD]Jan[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]2/1/2018[/TD]
[TD]Jan[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]3/1/2018[/TD]
[TD]Jan[/TD]
[TD="align: right"]9[/TD]
[/TR]
</tbody>[/TABLE]

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.

 

[sandy666]

maybe

[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

 

[VBA Geek]

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]

Hi VBA Geek,

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

[TABLE="width: 270"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Sum of date[/TD]
[TD]Max of month[/TD]
[/TR]
[TR]
[TD]1/1/2018[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]22[/TD]
[/TR]
[TR]
[TD]2/1/2018[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]22[/TD]
[/TR]
[TR]
[TD]3/1/2018[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]22[/TD]
[/TR]
</tbody>[/TABLE]

 

[VBA Geek]

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>

 

[oliver_wonder]

Thanks VBA Geek.
It is too much complicated than I thought.

 

[VBA Geek]

let me know if something is not clear