max of an calculate sum by week

[Wayne_Wu888]

Hi,

I have a table as below, which defines the No. of a persons and his corresponding workload for each week, I want to get a card telling me the maximum value of man_week summarize by week_ending regardless the "No.". I have tried different ways but still can't get it.

can you help me?

thanks in advance.

No.	Week_ending	Man_week
1	6/4/2021	1
1	6/11/2021	1
1	6/18/2021	1
1	6/25/2021	1
1	7/2/2021	1
1	7/9/2021	1
1	7/16/2021	1
1	7/23/2021	1
1	7/30/2021	1
2	7/2/2021	1
2	7/9/2021	1
2	7/16/2021	1
2	7/23/2021	1
2	7/30/2021	1
6	6/4/2021	0.5
6	6/11/2021	0.5
6	6/18/2021	0.5
6	6/25/2021	0.5
6	7/2/2021	0.5
6	7/9/2021	0.5
6	7/16/2021	0.5
6	7/23/2021	0.5
6	7/30/2021	0.5

[Anonymous]

Hi @Wayne_Wu888 

I don't quite get this... Can you please use a really simple but representative table with raw data (something like above) and show us the calculation "in slow motion," so to speak, and the final result you're expecting? This will then hopefully make it clear how to write the DAX. Thanks.

In case of doubts... please read THIS.

[Wayne_Wu888]

Hi @Anonymous ,

Here I have a line chart showing the curve of man_week for each week, the maximum value is 61, now I want to make a card telling me the number of "61" directly.

can you help?

[Anonymous]

One of the following will do what you want:

[Total Manhours] = sum( T[Man_week] )

// This one gives you the max manhours
// w.r.t. the selected people and 
// selected weeks.
[Max Manhours] =
CALCULATE(
    MAXX(
        SUMMARIZE(
            T,
            T[No.],
            T[Week_ending]
        ),
        [Total Manhours]
    ),
    ALLSELECTED( T )
)

// or

// This one gives you the max manhours
// w.r.t. the total for all selected
// people across the selected weeks.
[Max Manhours] =
CALCULATE(
    MAXX(
        DISTINCT( T[Week_ending] ),
        [Total Manhours]
    ),
    ALLSELECTED( T )
)

[CNENFRNL]

@Wayne_Wu888 , for you reference,

[Wayne_Wu888]

Hi @CNENFRNL,

Thanks for help, unfortunately that's not what i'm looking for.

actually i want to get the maxium value of summarize of Man_week by week_ending regardless the No., I have done this but it doesn't work properply. 

Peak Work_load =
VAR work_load =
SUMMARIZE(
'table',
'table'[Man_week],
"SUM work_load",SUM('table'[Man_week])
)
VAR Peak_work_load = MAXX(Work_load,[SUM Work_load])
RETURN
MAXX(FILTER(work_load,[SUM work_load] = Peak_work_load),'table'[Man_week])