cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kcantor Community Champion
Community Champion

Create a measure/calculated field

Greetings,

As a new PowerBI user, I am struggling wtih what is probably a simple DAX statement. Of course I have not successfully integrated this into PowerPivot either so all helap is appreciated.

In my current data, I have a field called TotalSales. When using this in the pivot table or in BI, I use the sum of total sales as the value and order year as columns. Rows vary by the type of information I am seeking. I use order week, category, and manufacturer as some examples for row lables.

What I need is a DAX formula to calculate a $variance and a % variance. In a pivot table, I can simply work outside the table and calculate the difference. IE: 2015Total Sales - 2014 Totals Sales, using cel references. This would return the $Variance. For the % variance, I use an IF statement: =IF(C7=0,"0",((D7-C7)/C7)) with C referencing 2014 sales and D referencing 2015 sales.

I am looking for a course in DAX but need a quick fix for this question. Can anyone here help me come up with a statement for these that would work in both tables?

Relevant Information:

Table Name: SalesPerformance Table

SalesTotals

OrderYear

Kind regards,

KCantor



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




1 ACCEPTED SOLUTION

Accepted Solutions
vlahoz
Frequent Visitor

Re: Create a measure/calculated field

Hello,

 

Create a measure with CALCULATE(SUM([Sales Totals]), SAMEPERIODLASTYEAR([DateColumn])), and then, calculate the variance and %.

 

Vicenç Lahoz

View solution in original post

3 REPLIES 3
Super User IV
Super User IV

Re: Create a measure/calculated field

Not sure of your exact data layout without example data.

 

Have a look here:

http://www.daxpatterns.com/time-patterns/

 


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

vlahoz
Frequent Visitor

Re: Create a measure/calculated field

Hello,

 

Create a measure with CALCULATE(SUM([Sales Totals]), SAMEPERIODLASTYEAR([DateColumn])), and then, calculate the variance and %.

 

Vicenç Lahoz

View solution in original post

ngottier
Regular Visitor

Re: Create a measure/calculated field

Is there any way to accomplish ^ when in direct query mode? Currently Power BI does not support PREVIOUSXXXXX, or LASTXXXX time look backs in direct query.

 

I would switch to import, but the data set is millions of rows and not really feasible to be an import.

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.