sum of max value per category

[Anonymous]

Hi, 

I need some help to write a DAX formula, I've tried so many already that I get lost now 😞 

Here is what I need as a final result, correct numbers are in green, wrong ones are the red (what I have for the moment) : 

Date	Destination	Number of available trains	Limit per day	percentage (number of available trains/limit per day)
18/08/2021	BELVAL	3	10	0.3
19/08/2021	BELVAL	2	10	0.2
18/08/2021	MERTERT	3	50	0.06
19/08/2021	MERTERT	4	50	0.08
Total		12	100  120	0.12    0.1

Global percentage : 0.1

My problem is to retrieve the correct Total of limit per day. Instead of having 120, I have 100 because I put a max(limit per day) to get the limit for each row. Here are how my data look like in the table : 

date                 |   train  |  destination |  limit    

18/08/2021     |  T1       |    BELVAL     |  10

18/08/2021     |  T2       |    BELVAL     |  10

18/08/2021     |  T3       |    BELVAL     |  10 

19/08/2021     |  T1       |    BELVAL     |  10

19/08/2021     |  T2       |    BELVAL     |  10

18/08/2021     |  T4       |    MERTERT  |  50

18/08/2021     |  T5       |    MERTERT  |  50

18/08/2021     |  T8       |    MERTERT  |  50

19/08/2021     |  T6       |    MERTERT  |  50

19/08/2021     |  T4       |    MERTERT  |  50

19/08/2021     |  T5       |    MERTERT  |  50

19/08/2021     |  T7       |    MERTERT  |  50

Can you please help me to get the green result instead of the red one ? Huge thanks for your help!!

[amitchandak]

@Anonymous , Use these two measures for the last 2

sumx(summarize(Table,[date], [train],[limit]), [Limit])

sumx(summarize(Table,[date], [train],"_1", divide(countrows(Table),max(Table[Limit]))), [_1])

[Anonymous · Accepted solution]

Hi amitchandak, 

Thanks for your answer but the 2 formulas don't bring the correct results 😕 It's weird you don't have the destination field in your formulas isn't it ?

However, I finally managed to write a formula that seems OK, can you please give me your feeling on it ?

M_percentagelimit =
VAR nbdays = countrows('DimDate')
VAR pct = sumx(
                         summarize(Disponibilite,Disponibilite[destination],"_1",max(Disponibilite[limit]))
                         ,[_1])
                        * nbdays
RETURN nb

Thanks, AnSo