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 ChangeFeb YOY ChangeMar YOY ChangeYOY Change Grand TotalValues20172018 20172018 20172018 Total Contacts(with IVR)143187 186144 115185 Total IVR Contacts187173 123141 170127 Total Contacts Forecast120192 106127 194102 Total Contacts Offered121121 170115 165159 Total Contacts Handled170154 168137 116128 Total Sales184146 175165 149185 Total Cost112185 199173 166197 Total Cost Plan166197 186154 135105 Total Cost Goal114168 174185 195117 Total Answer Rates 120105 174111 175102 Total Abandon Rate132165 177165 161178 Total Offer to Forecast144114 101193 160158 Total Cost Variance179107 183109 181193 Total Cost Variance(Goal)131163 110191 158176 Total CPC (Production)168124 174112 186135 Total CPC Agent143110 177168 187126 Total Average Speed of Answer144189 132112 117173 Total Average Handle Time187170 172178 200181 Total CPC (All)140168 115151 198159 Total CPH (All)121141 187192 196160 Total CPH (Production)158152 140109 113182 Total Conversion Rate154191 174191 170153 Total Average Order Size140174 138170 164115 Total Revenue Per Call158143 169174 119111 Total Revenue Per Cost180110 112118 115191 Total Occupancy Avg.137144 200139 164124 I am not sure if this is possible, but any help regarding this issue is greatly appreciated! Thank you in advance!

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

Anonymous

Not applicable

8 years ago

Solved

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!

Ashish_Mathur
8 years ago
Hi,

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

8 Replies

Greg_Deckler

Community Champion

8 years ago

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/ba-p/279563

Anonymous

Not applicable

8 years ago

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.

Ashish_Mathur

Super User

8 years ago

Hi,

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

Anonymous

Not applicable

8 years ago

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.

Ashish_Mathur
Super User
8 years ago
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.

Forum Discussion

Dynamic Measure Selection and YOY Calculation

8 Replies

Related content

Dynamic Rank Calculation

Dynamic Calculation Different Period

Quarter calculation upto selected month

Dynamic images based on selections

Dynamic Variable in Calculated Columns