Reply
upe Frequent Visitor
Frequent Visitor
Posts: 8
Registered: ‎08-08-2017
Accepted Solution

Matrix Calculations become incorrect when you drill into the data.

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

BI_Top_Level.PNG

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.

BI_Lower_Level.PNG

 

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


Accepted Solutions
upe Frequent Visitor
Frequent Visitor
Posts: 8
Registered: ‎08-08-2017

Re: Matrix Calculations become incorrect when you drill into the data.

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. 

View solution in original post


All Replies
upe Frequent Visitor
Frequent Visitor
Posts: 8
Registered: ‎08-08-2017

Re: Matrix Calculations become incorrect when you drill into the data.

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.

BI_Lower_Level_V2.PNG

 

 

 

 

 

Super Contributor
Posts: 3,609
Registered: ‎09-27-2016

Re: Matrix Calculations become incorrect when you drill into the data.

Hi @upe,

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

upe Frequent Visitor
Frequent Visitor
Posts: 8
Registered: ‎08-08-2017

Re: Matrix Calculations become incorrect when you drill into the data.

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

 

 % 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

upe Frequent Visitor
Frequent Visitor
Posts: 8
Registered: ‎08-08-2017

Re: Matrix Calculations become incorrect when you drill into the data.

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.