Formula to agregate data

[Charli]

Hello all,

I am working on PBI desktop

I am trying to write a formula to agregate some numbers in a precise order. To define the order, I have added a column next to my data with the correct "order" (1, 2, 3 etc.).

I have wrote the following formula : 

Calculation = if('Calculation'[nbre lignes]=1,CALCULATE([In M€],ALL('PnL Conso (nature)','PnL Conso (nature)'[Num order 2]<VALUES('PnL Conso (nature)'[Num order 2]))))

But then Power Bi is sending me the following error :  It is impossible to determine a unique value for the "Num order 2" column in the "PnL Conso (nature)" table. This can happen when a measurement formula refers to a column that contains many values, without specifying an aggregation such as min, max, count or sum to get a unique result.

I would appreciate any help please,

Thank you!

[Walter_W2022 · Accepted solution]

Hi @Charli , please try this, I assumed you want to calculate running total based on the order, it will be great if you can provide more details or data.

Calculation = 

VAR _MaxNum = MAX('PnL Conso (nature)'[Num order 2])

VAR _RT =

if(

'Calculation'[nbre lignes]=1,

CALCULATE(

[In M€],

FILTER(

ALL('PnL Conso (nature)'),

'PnL Conso (nature)'[Num order 2]<_MaxNum

)

)

)

RETURN

_RT

[Dangar332]

Hi, @Charli 

Please provide some data and what you want