cancel
Showing results for
Search instead for
Did you mean:
Highlighted
Regular Visitor

## Dynamic Measure Selection and YOY Calculation

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Dynamic Measure Selection and YOY Calculation

Hi,

Yes, you will have to write that formula for each measure.

8 REPLIES 8
Super User

## Re: Dynamic Measure Selection and YOY Calculation

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...

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Regular Visitor

## Re: Dynamic Measure Selection and YOY Calculation

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. ```

Super User

## Re: Dynamic Measure Selection and YOY Calculation

Hi,

I am not clear with your question.  What exactly do you want to do?  Please show the expected result.

Regular Visitor

## Re: Dynamic Measure Selection and YOY Calculation

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.

Super User

## Re: Dynamic Measure Selection and YOY Calculation

Hi,

Assuming you have:

1. A Calendar Table with a Month and Year column
2. A relationship from the Date column of yoru data table to the Date column of your Calendar table
3. 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.

Regular Visitor

## Re: Dynamic Measure Selection and YOY Calculation

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...

Super User

## Re: Dynamic Measure Selection and YOY Calculation

Hi,

Yes, you will have to write that formula for each measure.

Regular Visitor

## Re: Dynamic Measure Selection and YOY Calculation

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.