locked
how to get max sum at day grain of value? RRS feed

  • Question

  • Here's a mockup of a pivot table

    Cust	TranId	Date	Amt	MaxSumAtDayGrain
    a	323	1-Jan	5	10
    a	423	1-Jan	5	10
    a	562	2-Jan	4	10
    a	566	2-Jan	5	10
    b	325	1-Jan	2	5
    b	487	1-Jan	2	5
    b	532	2-Jan	4	5
    b	599	2-Jan	1	5
    

    I'm trying to write some dax to get the MaxSumAtDayGrain scoped to the cust. Basically the max of the amt summed at the day grain and for this to reset for each customer. And for this to also appear at lower levels (eg individual transactions)

    I've tried :=MAXX(values('Date'[Date]),calculate(sum([Amt]))) and it gives me the correct value at the cust level, but it falls apart at the lower grains. it just gives me the sum at that level

    I'm assuming i need to add a filter of some kind, but every logical combination i've tried has resulted to in the maxsumatdaygrain measure aggregating too many rows or not changing the result at the lower grains.


    Thanks


    Jakub @ Adelaide, Australia Blog

    Wednesday, January 14, 2015 6:39 AM

Answers

  • Hi,

    try something like this:
    MaxDailyAmtByCustomer:=MAXX(
        ALL(Table1[Date]);
        CALCULATE(
            SUM(Table1[Amt]);
            ALLEXCEPT(Table1; Table1[Cust]; Table1[Date])
    ))

    in case you also want to filter to a specific time-period (e.g. year) you may replace 
    ALL(Table1[Date]) with ALLSELECTED(Table1[Date])

    hth,
    gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    • Marked as answer by jakubk Friday, January 16, 2015 12:05 AM
    Thursday, January 15, 2015 8:06 AM
    Answerer

All replies

  • bump.

    sorry about the bump but my account seems to be suffering from this problem: https://social.msdn.microsoft.com/Forums/en-US/a6d4ac86-d862-44d1-8caf-ec2ab348a06f/i-posted-on-c-standard-extensions-and-interop-about-1-h-41-min-ago-but-my-post-doesnt-get?forum=reportabug

    my new threads dont appear in the forum view. I need to reply to them to get them to show up in the forums.


    Jakub @ Adelaide, Australia Blog

    Thursday, January 15, 2015 1:31 AM
  • Hi,

    try something like this:
    MaxDailyAmtByCustomer:=MAXX(
        ALL(Table1[Date]);
        CALCULATE(
            SUM(Table1[Amt]);
            ALLEXCEPT(Table1; Table1[Cust]; Table1[Date])
    ))

    in case you also want to filter to a specific time-period (e.g. year) you may replace 
    ALL(Table1[Date]) with ALLSELECTED(Table1[Date])

    hth,
    gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    • Marked as answer by jakubk Friday, January 16, 2015 12:05 AM
    Thursday, January 15, 2015 8:06 AM
    Answerer
  • Thanks,

    so I'd need to add every col in my main table into the allexcept list if I want it to be filter the result? My real data is giving me a value of 413.35 (max possible should be 24, the amt is number of hrs worked)

    It works against the dummy data I supplied so it answers my question.

    But this is more of an intermediate step I thought was necessary to actually get what I want. I think i'll start another thread with the full info of what I'm trying to accomplish. Burned ~10hrs on it so far and not quite there yet.


    Jakub @ Adelaide, Australia Blog

    Friday, January 16, 2015 12:05 AM
  • on Sub-/GrandTotal my calculation sums max daily values of all customers thats why you see something > 24

    to get the max per day and customer try this:

    MaxDailyAmtByCustomer:=MAXX(
        SUMMARIZE(
            Table1;
            Table1[Date];
            Table1[Cust]); 

        CALCULATE(
            SUM(Table1[Amt]); 
            ALLEXCEPT(Table1; Table1[Cust]; Table1[Date])
    ))

    hth,
    gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    Friday, January 16, 2015 8:32 AM
    Answerer