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
Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Upcoming Events

Community Summit North America – Join Online!

Join this community-driven Power Platform digital event for unbiased support and problem-solving.

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.

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Top Solution Authors