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

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  

Greg_Deckler's avatar
Greg_Deckler
Community Champion
3 years ago

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.