cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
norken20
Helper I
Helper I

Calculate Quarter Sales

Hi All,

 

I'm tryin?g to calculate the sum per quarter of a sales revenue but I can't figure out the best way to compute it. May I know of the best way how?

 

I have 2 columns table forexample below and want to get the total per quarter.

 

Revenue         Quarter

100                    Q1

200                    Q1

300                    Q1

330                    Q2

400                    Q2

600                    Q2

600                    Q3

300                    Q3

200                    Q4

100                    Q4

 

Thanks!

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

HI @norken20,

Any other categories or fields are you used in calculation? If this is a case, please explain more detail about your data structure.

How to Get Your Question Answered Quickly 
If not, you can simply use 'quarter' field right part as filter conditions to calculate.

Measure =
CALCULATE (
    SUM ( Table[Revenue] ),
    FILTER (
        ALLSELECTED ( Table ),
        RIGHT ( Table[Quarter], 1 ) <= RIGHT ( MAX ( Table[Quarter] ), 1 )
    )
)

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User
Super User

Hi,

Drag Quarter to your visual and write this measure

=SUM(Data[Revenue])

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-shex-msft
Community Support
Community Support

HI @norken20,

Any other categories or fields are you used in calculation? If this is a case, please explain more detail about your data structure.

How to Get Your Question Answered Quickly 
If not, you can simply use 'quarter' field right part as filter conditions to calculate.

Measure =
CALCULATE (
    SUM ( Table[Revenue] ),
    FILTER (
        ALLSELECTED ( Table ),
        RIGHT ( Table[Quarter], 1 ) <= RIGHT ( MAX ( Table[Quarter] ), 1 )
    )
)

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

amitchandak
Super User
Super User

Make sure you have a date table and date table can have a column Qtr name to sum data like this. You can also use time intelligence function for that

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))

Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))
Next QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],1,QUARTER)))

Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year)))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Thanks!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.