sum per product

[Anonymous]

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 

[v-shex-msft]

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]

@v-shex-msft

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 

[v-shex-msft]

Hi @Anonymous,

>>when i tried the formula for the table i got a two numbers 

If you create a table visual and add these measures to it, the visual will auto summary by each product.(if you drag them to card visual it only show two value)

Regards,

Xiaoxin Sheng

[ibarrau]

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,