cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
baxter2_nois102
Frequent Visitor

Get the Max Value from SUM group

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

 

baxter2_nois102_0-1627866641019.png

 

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

baxter2_nois102_1-1627866802309.png

 

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@baxter2_nois102 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.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

View solution in original post

1 REPLY 1
Greg_Deckler
Super User
Super User

@baxter2_nois102 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.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!