Forum Discussion

Charli's avatar
Charli
Frequent Visitor
2 years ago
Solved

Formula to agregate data

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's avatar
    Walter_W2022
    2 years ago
    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

2 Replies

  • Walter_W2022's avatar
    Walter_W2022
    Resolver II
    2 years ago
    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