cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ngupta Frequent Visitor
Frequent Visitor

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.

Region

C Estimate

D Estimate/Low Bid

(D-C) Difference

Variance from Estimate = (D-C)/C

South

790

603

-187

-23.7%

Central

1,988

2,000

12

0.6%

South

9,857

8,515

-1342

-13.6%

South

11,296

40

-11256

-99.6%

South

1,401

1,128

-273

-19.5%

North

273

350

77

28.2%

Total

25,605

350

 

-50.7%

 

I want it to calculate the variance between Estimate and Bid for various categories, but I need the Variance of the total.

Question: How do I get -50.7% in the graph, which is coming from “Total” Row.

 

What I am getting is the average of the variance  -21.3%, this is the average of the individual numbers

 

Average= (-23.7% +0.6% + -13.6% + -99.6% +-19.5% +28.2%)/6 =-21.3%

Right now I am calculating Variance (column 5) using the following measure: 

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

 

Please note numbers in the image below are same as numbers above, but divided by 1000 approx. 

 

 Capture.JPG

 

Variance calculation only gives me a few options, and none of those include calculation on the total SUm value. These rows od data will change based on several filters, but I always need the variance from the total value, and I cannot calculate that yet.

 

 

 

Capture2.JPG 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft v-ljerr-msft
Microsoft

Re: How to calculate using Total from columns

Hi @ngupta,

 

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

 

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

m1.PNGr1.PNG

 

Regards

View solution in original post

3 REPLIES 3
Microsoft v-ljerr-msft
Microsoft

Re: How to calculate using Total from columns

Hi @ngupta,

 

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

 

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

m1.PNGr1.PNG

 

Regards

View solution in original post

ngupta Frequent Visitor
Frequent Visitor

Re: How to calculate using Total from columns

Yes, this works. Thankyou Smiley Very Happy

I did try it earlier, but I was getting an error, probably because the syntax was wrong.

ChrissyG Frequent Visitor
Frequent Visitor

Re: How to calculate using Total from columns

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])

 Capture.PNG

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (2,230)