Skip to main content
|

PowerPivotPro's FAQ Site

Go Search
  
PowerPivotPro's FAQ Site > The Great PowerPivot FAQ > DAX Measure to Calculate % of Total Sales?  

The Great PowerPivot FAQ: DAX Measure to Calculate % of Total Sales?

Title

DAX Measure to Calculate % of Total Sales? 

Answer

Say that you have a report with days going down the side and regions going across the top.
 
=Sum(Fact[Sales]) will calculate the "filtered result" for each cell in the pivot table. For example, the top left result cell is at the intersection of Region=Central and Date=1/1/2010. If you don't specify any filters, the asking for Sum(Fact[Sales]) will automatically include the filters inherent in that cell's position in the table, i.e. Region=Central, Date=1/1/2010.
 
Frequently, you will want to calculate a ratio where the denominator calculation needs to ignore the filters inherent in that cell. To look at all sales in the denominator, use:
Calculate(Fact[Sales],All(Fact)).
 
The All([table]) function in DAX will retreive all of the sales figures in the entire table, not just Region=Central, Date=1/1/2010.
 
Thus, the syntax for the measure will be:
=Sum(Fact[Sales])/Calculate(Fact[Sales],All(Fact))

Client/Server

Client 

Topic

F - Calculations (DAX) 

Purpose

Information 

Answered?

Yes 

Contributor

Bill Jelen 

Título

 

Resposta

 

Frage

Wie erstelle ich mit DAX eine "%  des Gesamumsatzes" Kennzahl? 

Antwort

 

preRTM

 
Attachments
Created at 1/14/2010 9:51 AM  by Bill Jelen 
Last modified at 4/23/2010 1:28 PM  by Jochen Juelke