Forum Discussion

nishthabhakta's avatar
nishthabhakta
Regular Visitor
3 years ago
Solved

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 rati...
Need Help
  • nishthabhakta's avatar
    nishthabhakta
    3 years ago

    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's avatar
nishthabhakta
Regular Visitor
3 years ago
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
  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

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