Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

How to calculate using Total from columns

I have a very simple problem, but I am not good with DAX or even measures yet, so this may be preventing me from getting the answer.     I have a dataset with the following information. Regio...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi Anonymous,

     

    According to your description above, you should be able to use the formula below to create a measure(instead of a calculate column) to calculate the Variance in this scenario. :smileyhappy:

     

    Variance = DIVIDE(SUM(BI_TENDER_DIMS[CvsD Diff]),SUM(BI_TENDER_DIMS[C Estimate]))

     

    Regards

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

Hi Anonymous,

 

According to your description above, you should be able to use the formula below to create a measure(instead of a calculate column) to calculate the Variance in this scenario. :smileyhappy:

 

Variance = DIVIDE(SUM(BI_TENDER_DIMS[CvsD Diff]),SUM(BI_TENDER_DIMS[C Estimate]))

 

Regards

ChrissyG's avatar
ChrissyG
New Member
8 years ago

Hi, I am having a very similar issue but it seems like I am doing what you suggest and still getting an incorrect subtotal on my matrix. I am also very new to Bi, so I apologize if a super easy question, which I hope it is! I also may have done this completely wrong!  Thanks for your help

 

I am trying to show the month over month variance for revenue, which I have but the subtotal is giving me the last calculated number, instead of the total variance, which I want to be the total of all variances. In the example below the total should be $3,409,237.76.  also similar, is the additional formula to find the % variance. 

 

I used the following measures to get to this: 

MTD Rev = TOTALMTD(sum(Revenue[Total Revenue]),'Calendar'[Date])

LY MTD Rev = calculate([MTD Rev],SAMEPERIODLASTYEAR('Calendar'[Date]))

MTD Rev Var = [MTD Rev]-[LY MTD Rev]

 MTD Rev Var % = Divide([MTD Rev Var],[LY MTD Rev])