Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Matrix Calculations become incorrect when you drill into the data.

My top level calculations are correct as in this sample (all mock data):

The issue begins when I drill down into the data. I believe this has something to do with the row context, but I am not sure hot to fix this.

 

My % Change Qty Calc is:

% Change Qty = DIVIDE((SUM('CPC_RVU Provider_Management'[Service YTD Qty])-SUM('CPC_RVU Provider_Management'[Service LYTD Qty])),SUM('CPC_RVU Provider_Management'[Service LYTD Qty]))

 

My % Change RVU Calc is:

% Change RVU = Divide((SUM('CPC_RVU Provider_Management'[Service YTD RVU]) - SUM('CPC_RVU Provider_Management'[Service LYTD RVU])), SUM('CPC_RVU Provider_Management'[Service LYTD RVU]))

 

Any help is greatly appreciated. I am making the move from Tableau so I am still learning the subtlies of DAX.

 

Best regards,

upe

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Solved, finally!!!!

     

    I went back and figured out how to use the SUMX Function and used it to calcualte my Year To date (YTD) and Last Year To Date (LYTD)

     

    Used SUMX as it calcualtes at each row - and added a filter to grab current and last year.  This resolved the lower level data not calculating correctly.

     

    Service YTD QTY SUMX = SUMX(FILTER('CPC_RVU Provider_Management','CPC_RVU Provider_Management'[Service Year]=[Year Current]),'CPC_RVU Provider_Management'[Quantity])
    Service YTD RVU SUMX = SUMX(FILTER('CPC_RVU Provider_Management','CPC_RVU Provider_Management'[Service Year]=[Year Current]),'CPC_RVU Provider_Management'[RVU Total Value])

    When I use Divide to then calculate all my values were correct.

     

    Thank you to those who commented and helped me to find the answer. 

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    I got one step closer by reading and playing with filters.

     

    % Change Qty = 
    CALCULATE(DIVIDE((SUM('CPC_RVU Provider_Management'[Service YTD Qty])-SUM('CPC_RVU Provider_Management'[Service LYTD Qty])),
    SUM('CPC_RVU Provider_Management'[Service LYTD Qty])),
    ALL('CPC_RVU Provider_Management'[CPT & Description], 'CPC_RVU Provider_Management'[CPT Code]))

     

    By Adding Calculate and ALL filtered on the CPT & Description the Qty Calc is at least correct for the column, but not the individual row.  

     

    Still trying.

     

     

     

     

     

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi Anonymous,

    In your first screenshot, the [% Change Qty] column is corret, but [% Change RVU] is uncorrect. In your second screenshot, the  [% Change RVU] shows the "DIVIDE((SUM('CPC_RVU Provider_Management'[Service YTD Qty])-SUM('CPC_RVU Provider_Management'[Service LYTD Qty])),SUM('CPC_RVU Provider_Management'[Service LYTD Qty]))" values? Becasue,(16[Service YTD Qty]-21[Service LYTD Qty])/21[Service LYTD Qty]=-23.8%. It's really confusing, could you please share your sample table or .pbix file for further analysis?

    Best Regards,
    Angelia

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Many thanks for taking a look.

       

      I believe I have the % Change calculation correct for Qty and RVU - but I still can't figure out how ot make the row level (highlighted in yellow) calculate the row level change.

       

       % Change Qty Calc

       

      % Change Qty = CALCULATE(DIVIDE((SUM('CPC_RVU Provider_Management'[Service YTD Qty])-SUM('CPC_RVU Provider_Management'[Service LYTD Qty])),SUM('CPC_RVU Provider_Management'[Service LYTD Qty])),ALLSELECTED('CPC_RVU Provider_Management'[CPT & Description]))

      % Change RVU Calc

      % Change RVU = CALCULATE(Divide((SUM('CPC_RVU Provider_Management'[Service YTD RVU]) - SUM('CPC_RVU Provider_Management'[Service LYTD RVU])), SUM('CPC_RVU Provider_Management'[Service LYTD RVU])),ALL('CPC_RVU Provider_Management'[CPT & Description]))

      Many thanks for any help, it is greatly appreciated.

       

      I am not sure how to share the PBIX?  Is there a place to upload it here?

       

      Best regards,

      upe

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Solved, finally!!!!

         

        I went back and figured out how to use the SUMX Function and used it to calcualte my Year To date (YTD) and Last Year To Date (LYTD)

         

        Used SUMX as it calcualtes at each row - and added a filter to grab current and last year.  This resolved the lower level data not calculating correctly.

         

        Service YTD QTY SUMX = SUMX(FILTER('CPC_RVU Provider_Management','CPC_RVU Provider_Management'[Service Year]=[Year Current]),'CPC_RVU Provider_Management'[Quantity])
        Service YTD RVU SUMX = SUMX(FILTER('CPC_RVU Provider_Management','CPC_RVU Provider_Management'[Service Year]=[Year Current]),'CPC_RVU Provider_Management'[RVU Total Value])

        When I use Divide to then calculate all my values were correct.

         

        Thank you to those who commented and helped me to find the answer.