Forum Discussion
Solved
Sum same table, different filters
Good afternoon,
I have a question on how to make a sum of a field "Value" of the same table, with different types and relationship of Ids, this in the same table, I have an example in SQL of what has to be the query, as I ask this question in Power BI?
SELECT
L.*
FROM
LANCAMENTO L
JOIN LANCAMENTO L2 ON L.Id=L2.Id_Origem
WHERE
L.Vencimento BETWEEN '02/04/2019' AND '02/04/2019'
AND L.Tipo='PCR'
AND L2.Tipo='JR'
Hi lukkasmendes
Let me know if you'd like to get below result:
Measure 3 = var a = CALCULATE(MAX(L2[ID]),FILTER(L2,[Tipo]="JR")) var b = CALCULATE(MAX(L[ID]),FILTER(L,[Tipo]="PCR"&&[Date]=DATE(2019,2,4))) Return IF(a=b,SUMX(FILTER(L,[ID]=b),[Value]))
2 Replies
Hi lukkasmendes
Let me know if you'd like to get below result:
Measure 3 = var a = CALCULATE(MAX(L2[ID]),FILTER(L2,[Tipo]="JR")) var b = CALCULATE(MAX(L[ID]),FILTER(L,[Tipo]="PCR"&&[Date]=DATE(2019,2,4))) Return IF(a=b,SUMX(FILTER(L,[ID]=b),[Value]))
Easy Way is to create an alias
Go to Model view. In Model table click on New Table, and add new table formula
LANCAMENTO_l2 = LANCAMENTO
Rest is relation and filter as per need.
