Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
nishthabhakta
Regular Visitor

Calculation for quarters

I am trying to get numbers for quarters. I have a column that undicates return ratios. Return Ratio=Return Items/Originated Items.

I have made a new column named 'Column' to get row wise return ratios. However now I want quarter calculation but Power BI is summing up for months. How to get quarter calculation.PNG

1 ACCEPTED SOLUTION
nishthabhakta
Regular Visitor

Hi @Greg_Deckler ! I was able to solve the problem. I made a new measure with following formula :

Quarterly Average = CALCULATE(SUM('April 2021'[ReturnItem])/SUM('April 2021'[Originated])*100,ALLEXCEPT('April 2021','April 2021'[Date].[Quarter],'April 2021'[Date].[Year],'April 2021'[ProgramName],'April 2021'[ReturnItem],'April 2021'[Originated])) 
The above formula's output is as expected. I checked with calculating manually

View solution in original post

8 REPLIES 8
nishthabhakta
Regular Visitor

Hi @Greg_Deckler ! I was able to solve the problem. I made a new measure with following formula :

Quarterly Average = CALCULATE(SUM('April 2021'[ReturnItem])/SUM('April 2021'[Originated])*100,ALLEXCEPT('April 2021','April 2021'[Date].[Quarter],'April 2021'[Date].[Year],'April 2021'[ProgramName],'April 2021'[ReturnItem],'April 2021'[Originated])) 
The above formula's output is as expected. I checked with calculating manually
nishthabhakta
Regular Visitor

Quarter Ratio Column =
  VAR __Year = 'Return Ratio'[Date].[Date]
  VAR __Quarter = QUARTER([Date].[Date])
  VAR __Table = FILTER('Return Ratio',YEAR([Date])=__Year && QUARTER([Date])=__Quarter)
RETURN
  DIVIDE(SUMX(__Table,[ReturnItem]),SUMX(__Table,[Originated]),0
 Do I need to change something in the formula

@nishthabhakta I think I see your problem, you are missing the YEAR function for your __Year VAR.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
nishthabhakta
Regular Visitor

nishthabhakta
Regular Visitor

@Greg_Deckler Hey greg! Still some issue with the number. If I sum up the Originated Items for Quarter 1 of 2021 it is 1258751 and sum of quarter 1 2021 return items come up to 37034. When we divide 37034/1258751 the number we get is 0.0294. Also if I plug the new column in a visual, it is still summing up the rows  

@nishthabhakta You can solve the summing problem by doing an average instead of a sum. Or use a Quarter column and a measure like in the attached file below signature. Don't ever use the auto date hierarchy. Not sure if that is the issue but bad. I checked my column calculation against a simple measure and they both return the same result. See file. You want 'Table7'. The Measure 4 by Quarter visual on the Page 1.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Greg_Deckler
Super User
Super User

@nishthabhakta Maybe:

Quarter Ratio Column =
  VAR __Year = YEAR([Date])
  VAR __Quarter = QUARTER([Date])
  VAR __Table = FILTER('Table',YEAR([Date])=__Year && QUARTER([Date])=__Quarter)
RETURN
  DIVIDE(SUMX(__Table,[ReturnItem]),SUMX(__Table,[Originated]),0)

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thanks! I will try this one

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.