Forum Discussion
sum per product
Hi
I have this data example
and this should be the result
I know you can do it by slicers but i would like to get a formula which will basicaly do anual sale per product
thank you
4 Replies
- ibarrau
Super User
I'm not sure I get the problem here.
If you already have the years, just make a matrix with product as rows and sum of Unit Sold2016 and Unit Sold2017 as values.
The normal way to go is to have amounts and years in two columns and not a columns for each year. In that case, make a matrix with product as rows, years as column and sum of amount as values.
Hope this helps.
Regards,
Hi Anonymous,
You can try to use below formula to calculate the total value of each product.
Measures: Sum of each product 2017= var currProduct= LASTNOBLANK(Table[Product],[Product]) return SUMX(FILTER(ALL(Table),[Product]=currProduct),[Units sold 2017]) Sum of each product 2016= var currProduct= LASTNOBLANK(Table[Product],[Product]) return SUMX(FILTER(ALL(Table),[Product]=currProduct),[Units sold 2016])
In addition, you can also use summarzie function to get the summary table.
Table formula:
Summary Table= SUMMARIZE(Table,[Product],"Sum 2017",SUM(Table[Units sold 2017]),"Sum 2016",SUM(Table[Units sold 2016]))
Regards,
Xiaoxin Sheng
Anonymous
I feel I am getting closer
when i tried the formula for the table i got a two numbers .. so basicaly total sale for 2016 and 2017.
what I need is to have the sale per product but i would like to have it as meassure
lets say
2017 2016
Jordan 23 137
Airmax 14 18
Airforce 16 5
Nike Dunk 2 45
i tried something like this but it doesnt really work.
sales per product 17=
var uniqueproduct=DISTINCT('table'[product])
return
sumx(filter('table',[product]=uniqueproduct),[units sold 2017]))
thank you
