Get the Max Value from SUM group

[Anonymous]

Hi, 

I am new in PBI, and I am trying to get something similar to this SQL query:

select b.[DescripcionBancos],SUM([ImporteDlls]),[IdDerivadoTipo]
from [dbo].[Fact_IndicadoresSIF] a
join [dev_fin].[sif_vDimBancos] b
on a.IdBanco=b.IdBanco
where [CalendarioID]=20180331
and [DescripcionBancos] in ('BBVA BANCOMER','HSBC','RABOBANK','SANTANDER')
AND Concepto='CCS'
Group by b.[DescripcionBancos],[IdDerivadoTipo]
order by 1,2

Basically I am doing a SUM but I am getting the TOTAL and not the MAX(IdDerivadoTipo), I am using this Metric but I dont' know hot to adjust it in order to group the data and take the MAX (those values with 3):


PosicionDerivados_LineaLimite:=
CALCULATE(SUM(FactIndicadoresSIF[ImporteDlls])
,FactIndicadoresSIF[Indicador] IN {"Derivados","Ind. Derivados"}
,FactIndicadoresSIF[Concepto]="CCS")

This is the chart but it shoud have 20 in BBVA, HSBC is correct, RABOBANK should be 10 and Santander should be 16

[Greg_Deckler · Accepted solution]

@Anonymous I would generally do something like:

PosicionDerivados_LineaLimite:=
  VAR __Table = 
    SUMMARIZE(
      FILTER(FactIndicadoresSIF, FactIndicadoresSIF[Indicador] IN {"Derivados","Ind. Derivados"} && FactIndicadoresSIF[Concepto]="CCS"),
      [IdDerivadoTipo],
      "__Sum",SUM(FactIndicadoresSIF[ImporteDlls])
    )
RETURN
  MAXX(__Table,__Sum)

If I understand your code. Basically, use SUMMARIZE, SUMMARIZECOLUMNS or GROUPBY to group your filtered rows by the specified colum and essentially add a sum as a column. Then use MAXX to find the MAX across that sum.