Forum Discussion

vijayit43's avatar
vijayit43
Frequent Visitor
9 years ago
Solved

How to Use Aggregate function In PowerBi

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 ItemQty
2017Item110
2017Item28
2017Item39
2017Item47
2017Item512
2016Item111
2016Item26
2016Item55
2016Item68
2016Item79

 

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.

Need Help
Show and Tell
  • TomMartens's avatar
    TomMartens
    9 years ago

    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

1 Reply

  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    9 years ago

    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