2 views
in BI
edited

How can I calculate the average sum of sales for each product year wise

```Table1

Product Year    SalesAM SalesPM State
Pants   2015    6477    5787    California
Pants   2015    9675    7967    NewYork
Pants   2016    4577    5785    California
Pants   2016    6467    9357    NewYork
Pants   2017    2524    9679    California
Pants   2017    5745    4674    NewYork
Pants   2018    3532    6585    California
Pants   2018    6746    9575    NewYork
Shirts  2015    3452    9577    California
Shirts  2015    3464    6896    NewYork
Shirts  2016    2452    5785    California
Shirts  2016    5685    9679    NewYork
Shirts  2017    3635    4674    California
Shirts  2017    3435    9457    NewYork
Shirts  2018    5745    5688    California
Shirts  2018    2355    7585    NewYork
Shoes   2015    6653    9657    California
Shoes   2015    6766    9678    NewYork
Shoes   2016    5684    5788    California
Shoes   2016    5745    9577    NewYork
Shoes   2017    6887    7976    California
Shoes   2017    2454    4746    NewYork
Shoes   2018    4674    3567    California
Shoes   2018    6744    9686    NewYork```

How can I do this,and I want to know the DAX function for it?

by (22.5k points)
edited

Use the following measures to acheive that

`AVG AM = AVERAGE('Table'[SalesAM])`

Measure = CALCULATE(SUMX(SUMMARIZE('Table', 'Table'[product], 'Table'[State],"avg",[AVG AM]), [avg]), ALLEXCEPT('Table', 'Table'[Year], 'Table'[Product]))

SUMMARIZE('Table', 'Table'[product], 'Table'[State],"avg", [AVG AM], [avg])

CALCULATE(SUMX(SUMMARIZE('Table', 'Table'[product], 'Table'[State], "avg", AVERAGE('Table'[SalesAM])), [avg]), ALLEXCEPT('Table', 'Table'[Year], 'Table'[Product]))

Check the power bi training provided by intellipaat

Also, check out our YouTube video to known the fundaments fundamentals on Power Bi