topic Re: Formula to agregate data in DAX Commands and Tips

Formula to agregate data

Charli — Wed, 25 Oct 2023 16:46:36 GMT

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!

Re: Formula to agregate data

Dangar332 — Wed, 25 Oct 2023 18:39:46 GMT

Hi, Charli

Please provide some data and what you want

Re: Formula to agregate data

Walter_W2022 — Wed, 25 Oct 2023 21:34:12 GMT

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