Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |