Forum Discussion
Dynamic Measure Selection and YOY Calculation
Hi,
Yes, you will have to write that formula for each measure.
Hi,
I am not clear with your question. What exactly do you want to do? Please show the expected result.
Hello,
Once again thanks for taking the time to responsd to my question the expected result:
Jan YOY Change Feb YOY Change Mar YOY Change YOY Change Grand Total Values 2017 2018 2017 2018 2017 2018 Total Contacts(with IVR) 143 187 186 144 115 185 Total IVR Contacts 187 173 123 141 170 127 Total Contacts Forecast 120 192 106 127 194 102 Total Contacts Offered 121 121 170 115 165 159 Total Contacts Handled 170 154 168 137 116 128 Total Sales 184 146 175 165 149 185 Total Cost 112 185 199 173 166 197 Total Cost Plan 166 197 186 154 135 105 Total Cost Goal 114 168 174 185 195 117 Total Answer Rates 120 105 174 111 175 102 Total Abandon Rate 132 165 177 165 161 178 Total Offer to Forecast 144 114 101 193 160 158 Total Cost Variance 179 107 183 109 181 193 Total Cost Variance(Goal) 131 163 110 191 158 176 Total CPC (Production) 168 124 174 112 186 135 Total CPC Agent 143 110 177 168 187 126 Total Average Speed of Answer 144 189 132 112 117 173 Total Average Handle Time 187 170 172 178 200 181 Total CPC (All) 140 168 115 151 198 159 Total CPH (All) 121 141 187 192 196 160 Total CPH (Production) 158 152 140 109 113 182 Total Conversion Rate 154 191 174 191 170 153 Total Average Order Size 140 174 138 170 164 115 Total Revenue Per Call 158 143 169 174 119 111 Total Revenue Per Cost 180 110 112 118 115 191 Total Occupancy Avg. 137 144 200 139 164 124 Is a slicer that can filter the table down to one measure or more and calulate the difference percent year over year and the YTD percent difference for each measure:
Jan Feb Grand Total
YOY Change YOY Change YTD change/Grand Total Chage Values 2017 2018 2017 2018 Total Contacts(with IVR) 143 187 30% 186 144 0.01% Some of the measures above are a simple sum, but some of the measures are sum products one cannot just simply add them to get the percent change. I have all the measures working correctly and the grand totals are doing calculatting according to the written measure. I just can't change the measure or calculate the yoy over year and YTD change for each meausre. Hopefully, that clears it up.
Hi,
Assuming you have:
- A Calendar Table with a Month and Year column
- A relationship from the Date column of yoru data table to the Date column of your Calendar table
- Dragged Year and Month from the Calendar table into your matrix visual
you should be able to use measures such as
YoY = [Amount]/CALCULATE([Amount],SAMEPERIODLASTYEAR(Calendar[Date]))-1
where Amount is a measure such as =SUM(Data[Total Sales])
Hope this helps.
Hi,
Once again thank you for your response. I think I maybe making the question more complicated that it has to be. Essentially I am just trying to see how to filiter the measures(about 30) in the format seen above(the first table filter that row). I think I've got the YOY calculation, but just to be sure For the YOY calculation do I have to right that for each measure? Where Amount= SUM(Sales[Data]))
SUM(Sales[Quantity)...
etc...