Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 ChangeFeb YOY ChangeMar YOY ChangeYOY Change Grand Total
Values20172018 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!

1 ACCEPTED SOLUTION

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

8 REPLIES 8
Ashish_Mathur
Super User
Super User

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hello,

 

Once again thanks for taking the time to responsd to my question the expected result: 

 

           
 Jan YOY ChangeFeb YOY ChangeMar YOY ChangeYOY Change Grand Total
Values20172018 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  

 

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 ChangeYTD change/Grand Total Chage   
Values20172018 20172018     
Total Contacts(with IVR)143187 30%186144 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:

 

  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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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.

Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.