cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Microsoft
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
Highlighted
Microsoft
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

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

Highlighted
New Member

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

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors