Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I am fairly new to Dax and M langauage, but I have mananged to throw a report together that works, but it is missing some dyamic features(Please forgive any terminologies that are incorrect.
Below I am trying to dynamically fillter the values column(which are all calculated measures) while also calculating the YOY change:
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 |
I am not sure if this is possible, but any help regarding this issue is greatly appreciated! Thank you in advance!
Solved! Go to Solution.
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:
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...
Hi,
Yes, you will have to write that formula for each measure.
Great! I know where to go from there. In terms of filtering the measures would you be able to provide a little more insight as how to that works. I went through the previous suggested solution and was not able to filter the measures. Once again thank you for your help! This has been a big road block in using measures for advanced calculations and measures.
In general, to use a measure in that way, you need to use the Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick...
Thank you for your response! I appreciate you reponse. Just to confirm my code's reasoning would be:
Measures to Show = IF( // This first IF condition forces the measure to evaluate in the context of the visual HASONEVALUE(Employees[Employee]), Is this repeated for each columun? In my case the each measure is a column before I switch
the measure to rows for the table visual (Contact c is table name in the table above).
Example: HASONEVALUE(ContactC[Total Contacts],
HASONEVALUE(ContactC[Total CPH],
// This next condition avoids getting the error that the visual cannot be displayed IF(HASONEVALUE(Attendance[Attendance]), I assume this is a the disconnected table created with the names of all the measures( I will name it MeasureC). // This switch statement uses the values from our Attendance slicer to determine which measure to display SWITCH( VALUES(Attendance[Attendance]), Should the values for MeasureC be an index or does this just refer to the names of all the measures? "Attended",[Attended], Here I just reapeat each measure again I assume it refers to the names. "Not Attended",[NotAttended] ), // If the Attendance slicer has not been selected, just display the date of the training MAX([Date]) ) )
I guess I am having a hard time seeing how the disconnected table connects to the table I just in the previous post without having to rewrite the formulas of the calculated measure.
Once again thanks for the repsonse. I have been trying to figure this out for a while now.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
116 | |
104 | |
77 | |
71 | |
50 |
User | Count |
---|---|
146 | |
107 | |
106 | |
89 | |
65 |