Skip to main content
|

PowerPivotPro's FAQ Site

Go Search
  
PowerPivotPro's FAQ Site > The Great PowerPivot FAQ > DAX Measure to calculate % of today's sales?  

The Great PowerPivot FAQ: DAX Measure to calculate % of today's sales?

Title

DAX Measure to calculate % of today's sales? 

Answer

Say that you have a report with days going down the side and regions going across the top.
 
Consider the top left value cell, at the intersection of Region=Central and Date=Jan 1:
You want to calculate the Central Region's sales on the first of January compared to total sales for all regions for the first of January.
 
For the numerator, use: =Sum(Fact[Sales]). This will give you the filtered sales for that particular cell. This calculation inherently includes virtual filters of Region=Central, Date=Jan 1.
 
For the denominator, you want to write DAX that will continue to respect the inherent filter for the date, but no longer filter by Region. Restating this another way, you want to ignore all filters except for the date filter.
 
For the denominator, use: Calculate(Fact[Sales],AllExcept(Fact,Fact[Date])). This will give you total sales for the date in the column field for each row of the pivot table.
 
The syntax for the measure is:
=Sum(Fact[Sales])/Calculate(Fact[Sales],AllExcept(Fact,Fact[Date]))

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 heutigen Umsatzes" Kennzahl? 

Antwort

 

preRTM

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