Maximum Value with IF Condition

[sakolwakar]

I'm trying to calculate the maximum value with multiple conditions in Power BI.

The dataframe is:

Year    Company         Bond            Branch      Group     Type    Value
2016    BANCO DO BRASIL INDEPENDENTE    RISK        RETAIL    NOMINAL 4061567
2016    BANCO DO BRASIL INDEPENDENTE    ACUMULAÇÃO  RETAIL    NOMINAL 1901920
2017    BANCO DO BRASIL INDEPENDENTE    RISK        CORPORATE REAL    439499
2017    BANCO DO BRASIL INDEPENDENTE    RISK        RETAIL    REAL    356231
2016    BRADESCO        INDEPENDENTE    RISK        CORPORATE NOMINAL 347369
2016    BANCO DO BRASIL INDEPENDENTE    ACUMULAÇÃO  RETAIL    REAL    310920
2016    BANCO DO BRASIL LIGADO A BANCO  RISK        CORPORATE NOMINAL 12091
2016    BANCO DO BRASIL INDEPENDENTE    ACUMULAÇÃO  RETAIL    REAL    1021
2017    BANCO DO BRASIL INDEPENDENTE    RISK        CORPORATE REAL    446

I want to create a column with the maximum value by year, Bond, Branch, Group and Type disconsidering Company

I've already tried the following code:

MAX = CALCULATE(MAX(data[Value]);
      FILTER(ALLEXCEPT(data;data[Company];
      data[Year] = data[Year] 
      && data[Branch] = data_segmento_anual[Branch]
      && data_segmento_anual[Group] = data_segmento_anual[Group]
      && data_segmento_anual[Bond] = data_segmento_anual[Bond]
      && data_segmento_anual[Type] = data_segmento_anual[Type]))

I'm expecting this result:

Year    Company             Bond            Branch      Group     Type    Value   MAX 
    2016    BANCO DO BRASIL INDEPENDENTE    RISK        RETAIL    NOMINAL 4061567 4061567
    2016    BANCO DO BRASIL INDEPENDENTE    ACUMULAÇÃO  RETAIL    NOMINAL 1901920 1901920 
    2017    BANCO DO BRASIL INDEPENDENTE    RISK        CORPORATE REAL    439499  439499
    2017    BANCO DO BRASIL INDEPENDENTE    RISK        RETAIL    REAL    356231  356231
    2016    BRADESCO        INDEPENDENTE    RISK        CORPORATE NOMINAL 347369  347369
    2016    BANCO DO BRASIL INDEPENDENTE    ACUMULAÇÃO  RETAIL    REAL    310920  310920
    2016    BANCO DO BRASIL LIGADO A BANCO  RISK        CORPORATE NOMINAL 12091   12091
    2016    BANCO DO BRASIL INDEPENDENTE    ACUMULAÇÃO  RETAIL    REAL    1021    310920
    2017    BANCO DO BRASIL INDEPENDENTE    RISK        CORPORATE REAL    446     439499

[Kalpavruksh · Accepted solution]

Hi,

Your ALLEXCEPT is removing all context except for Company, which is the opposite of what you want. Put what you want to keep inside ALLEXCEPT.

CALCULATE (
    MAX ( data[Value] );
    ALLEXCEPT (
        data;
        data[year];
        data[Branch];
        data_segmento_anual[Bond];
        data_segmento_anual[Group];
        data_segmento_anual[Type]
    )
)

The other way to do it is to use ALL to specify the column that you want to be ignored.

CALCULATE ( MAX ( data[Value] ); ALL ( data[Company] ) )

Kalpavruksh Technologies | Microsoft Gold Partner
Denmark | USA | India | Germany

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sakolwakar]

Thank you so much @Kalpavruksh for assisting me in this solution. I was stuck at this and needed inputs immediately and didn't know and was not much sure if I will get any input on the same. The questions seems to be pretty complex and you solved it easily.

Again thanks much!

[sakolwakar]

Thanks for the input @Kalpavruksh  Let me see how this works 🙂