How to Use Aggregate function In PowerBi

[vijayit43]

Dear All,

             We want to Use Aggregate function in Power Bi but we unable to use Aggregate Function. For Example We have Multiple Items Sale Available in Year 2017 and 2016. we want Current Year Item sum which Item already available in Year 2016. for Example.

Year	Item	Qty
2017	Item1	10
2017	Item2	8
2017	Item3	9
2017	Item4	7
2017	Item5	12
2016	Item1	11
2016	Item2	6
2016	Item5	5
2016	Item6	8
2016	Item7	9

Item 1 to 5 sale available in 2017 and Item1,Item2,Item5,Item6,Item7 sale in 2016,So Item1, Item2 Item5 available in Both Year so we Want Sum Item1,Item2&Item5 in 2017 is (30) how to achieve in Power BI Please suggest.

[TomMartens · Accepted solution]

Hey,

this measure

Measure = 
var currentYear = MAXX(VALUES('Table1'[Year]),'Table1'[Year])
var itemsMaxYear =
	SELECTCOLUMNS( 
		FILTER(
			ALL('Table1')
			,'Table1'[Year] = currentYear
		)
		,"Item", 'Table1'[Item]
	)
var itemsPrevYear =
	SELECTCOLUMNS( 
		FILTER(
			ALL('Table1')
			,'Table1'[Year] = currentYear - 1
		)
		,"Item", 'Table1'[Item])
var itemsIntersect = 
	INTERSECT(itemsMaxYear, itemsPrevYear)
return
CALCULATE(
	SUM(Table1[Qty])
	,FILTER(
		ALL('Table1')
		,'Table1'[Year] = currentYear &&
		'Table1'[Item] in itemsIntersect
	)
)

creates this output

First the "current" year is stored in a variable. Does the column Year not contribute to the Filter Context (the card visual, and the total row of the table visual, the DAX statement makes sure that the max value is stored to the variable.

This variable is used to create two table variables containing the items of the current year and the previous year (either from the row context or the latest year, using SELECTCOLUMNS(FILTER(...),...)

A 3rd table variable is calculated that finally contains the items present in both years

This 3rd table variable is finally used to filter down the table in combination with the variable currentYear.

Hope this helps

Regards

Tom