Forum Discussion
Maximum Value with IF Condition
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- 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.
3 Replies
- Kalpavruksh
Resolver I
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.Thanks for the input Kalpavruksh Let me see how this works :-)
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!