Back

Explore Courses Blog Tutorials Interview Questions
0 votes
2 views
in BI by (11.1k points)
edited by

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? 

1 Answer

0 votes
by (22.5k points)
edited by

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])) 

enter image description here

Check the power bi training provided by intellipaat 

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

Related questions

0 votes
1 answer
0 votes
1 answer
asked Mar 2, 2021 in BI by Chris (11.1k points)
0 votes
1 answer
asked Dec 1, 2020 in BI by Chris (11.1k points)
0 votes
1 answer
asked Jan 14, 2021 in BI by Chris (11.1k points)

Browse Categories

...