Forum Discussion

Anonymous's avatar
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 incorrec...
  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    Hi,

     

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

Ashish_Mathur's avatar
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's avatar
Anonymous
Not applicable
8 years ago

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. 

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

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

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        8 years ago

        Hi,

         

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