THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
You can follow me on Twitter: @marcorus

How CALCULATE works in DAX

The CALCULATE function in DAX is the magic key for many calculations we can do in DAX. However, it is not pretty intuitive how it works and I spent a lot of time trying to understand how it can be used.

First of all, this is the syntax.

CALCULATE( <expression>, <filter1>, <filter2>… )

The expression that we put in the first parameter has to be evaluated to return the result (that is a value, not a table). For this reason, the expression is usually an aggregation function like SUM, MIN, MAX, COUNTROWS and so on.

This expression is evaluated in a context that is modified by the filters in the following parameters. A key point is that these filters can both enlarge and restrict the current context of evaluation. Let’s try to understand what it means by considering a few examples. The following table is the one we import in PowerPivot in a table named Demo.

Calculate-01

If we project the count of Rows in a PivotTable putting the Name on the Rows, we get the following result:

Calculate-02

Now, we might have the need to calculate a measure which is not affected by the selection of the Name or, in other words, which always calculate the context for all the names. Thus, we define this calculated measure:

CountAllNames = CALCULATE( COUNTROWS('Demo'), 
                           ALL( 'Demo'[Name] ) )

We obtain the CountAllNames column in the PivotTable that always returns the number of all the rows of the Demo table, without considering the filter on the Name.

Calculate-03

However, if we add Color attribute to the rows of the PivotTable, the CountAllNames is filtered by that attribute too. In fact, for each color, we have 2 rows for a Name (see Count of Rows column) and 6 rows considering all the names (see CountAllNames column).

Calculate-04

Now, we can also add a Boolean expression as a filter of the CALCULATE function. For example, we might filter just the Car transport.

CountAllNamesCar = CALCULATE( COUNTROWS('Demo'),
                              ALL( 'Demo'[Name] ),
                              'Demo'[Transport] = "Car" )

In this case, we will reduce the CountAllNamesCar column for a color of Emily to 3, because the number of rows with color Green and Car transport are 3.

Calculate-05

At this point we might wonder whether the Calculate filter parameters are enlarging or restricting the context of evaluation. The rule is the following one.

If the current context has a filter on a column of a PowerPivot table (which is a selection of a PivotTable, regardless it is a slicer, a report filter or a row/column selection), any reference for that column in one or more filter parameters of the Calculate function replaces the existing context. Then, the filters specified in the CALCULATE parameters are combined together like they were in an AND condition of a WHERE clause of a SQL SELECT statement.

For instance, consider a filter on the Color green using a Boolean expression in the CALCULATE function:

ColorGreen = CALCULATE( COUNTROWS('Demo'),
                        'Demo'[Color] = "Green" )

A Boolean expression used as a filter parameter in a CALCULATE function corresponds to an equivalent FILTER expression that operates on all the values of a column (for this reason, you can only a single column can be used in a Boolean expression that is used as a table filter expression):

ColorGreen = CALCULATE( COUNTROWS('Demo'),
                        FILTER( ALL('Demo'[Color]), 'Demo'[Color] = "Green" ) )

We obtain that ColorGreen column always filters by color Green and each Name has only 2 rows with color Green.

Calculate-06

Any filter expression in a CALCULATE statement overrides the existing selection of the PivotTable for the columns it contains. In the previous rule we highlighted the “reference” definition, because the FILTER that is internally used in place of the Boolean expression uses a FILTER expression that returns a set of values for the Color column. Thus, the existing selection for the color (the color is in fact specified in the rows of the PivotTable) is overridden by our filter and only green rows in the source table are considered to calculate the ColorGreen measure value. The true reason we lose the current selection on the color attribute is that the ALL( Demo[Color] ) expression returns a set of all the color values and ignores the existing selection.

If we don’t want to lose the existing selection of the PivotTable (that means that we don’t want to lose the existing filters on the calculation context), we can simply use in the FILTER expression a function that doesn’t ignore the existing selection. Instead of using the ALL( Demo[Color] ) expression as the source of the filter, we can use the VALUES( Demo[Color] ) expression, which keeps existing selections and returns the values still available in the color attribute.

In fact, if we use the following calculated measure:

ColorGreen = CALCULATE( COUNTROWS('Demo'),
                        FILTER( VALUES('Demo'[Color]), 'Demo'[Color] = "Green" ) )

We obtain as a result that the Color filter in the PivotTable is still active, and returns no rows for all the colors but green.

Calculate-07

We can see that the FILTER expression in a CALCULATE function always replaces the previous context for the referenced columns. However, we can save the existing context by using an expression which uses the existing context and further restricts the members we want to consider for one or more columns. And this is what we have done using VALUES formula instead of ALL as the first parameter of the FILTER call.

Thanks to Marius Dumitru, the various combination of FILTER, ALL, VALUES in a CALCULATE statement can be summarized in this way.

= CALCULATE(
   
COUNTROWS('Demo'),
    FILTER( ALL('Demo'[Color]), 'Demo'[Color] = "Green" ) )
    -- ignores/replaces existing Color filters and sets a filter on Green

= CALCULATE(
    COUNTROWS('Demo'),
    FILTER( VALUES('Demo'[Color]), 'Demo'[Color] = "Green" ) )
    -- keeps existing Color filters and adds a further filter on Green

= CALCULATE(
   
COUNTROWS('Demo'),
    FILTER( ALL('Demo'[Color]), 'Demo'[Color] = "Green" ),
    VALUES('Demo'[Color])
)
    -- same as the previous expression (keeps existing Color filters)
    -- NOTE: the first filter would consider all the colors, but the second
    -- expression (VALUES) only consider the current selection and
    -- the two filters will be considered using an AND condition, thus

= CALCULATE(
    COUNTROWS('Demo'),
    FILTER( ALL('Demo'), 'Demo'[Color] = "Green" ) )
    -- ignores/replaces filters on all Demo columns,
    -- and sets a filter on all columns (Name, Transport etc., not just Color)
    -- with rows that meet the condition

Finally, some words of caution have to be spent to the first parameter we pass to the FILTER function. If we consider this expression:

ColorGreen = CALCULATE( COUNTROWS('Demo'),
                        FILTER( 'Demo', 'Demo'[Color] = "Green" ) )

We pass the whole Demo table to the FILTER condition, which results a filter of the current context with all the columns! In this way we apply a restrictions on the color green and we get the same result as before (no rows for all the colors but green, the selection of color of the PivotTable is still applied) but, remember, the FILTER is returning ALL the rows. What does it mean?

Well, consider a further selection on the PivotTable where the Transportation attribute is filtered by Bike. This is the result using the ColorGreen definition we have just defined.

Calculate-08

Now, let’s add another filter to the CALCULATE function, filtering also the rows with Trasport equals to Car.

CarGreen = CALCULATE( COUNTROWS('Demo'),
                      FILTER( 'Demo', 'Demo'[Color] = "Green")
                      'Demo'[Transport] = "Car" )

which, as we have seen before, corresponds to:

CarGreen = CALCULATE(
               COUNTROWS('Demo'),
               FILTER( 'Demo', 'Demo'[Color] = "Green")
               FILTER( ALL( 'Demo'[Transport] ), 'Demo'[Transport] = "Car" ) )

The results is that the filter for Bike defined in the PivotTable plus the filter for Car defined in the CarGreen measure returns no rows at all in the CarGreen result. If we think about it, why this happens is not very intuitive at this point!

Calculate-09

Our filter on the Transport column is actually restricting the current selection and is not replacing it! But, wait, if we write just the filter on Transport, without the filter on Color, we have:

TransportCar = CALCULATE( COUNTROWS('Demo'),
                          'Demo'[Transport] = "Car" )

that produces the following result, which replaces the Bike selection of the PivotTable in the TransportCar column!

Calculate-10

This last calculation (TransportCar) is simple to explain, because it has the same behavior we have seen before with the first ColorGreen calculated measure, where the filter of the color green replaced any existing color selection in the PivotTable. The difference in the CarGreen calculation is the other filter parameter, which returns all the columns from the Demo table. Consider the highlighted filter in the CarGreen formula:

CarGreen = CALCULATE( 
              COUNTROWS('Demo'), 
              FILTER( 'Demo', 'Demo'[Color] = "Green"),
              FILTER( ALL( 'Demo'[Transport] ), 'Demo'[Transport] = "Car" ) )

The filter on color green returns all the columns of the current context. If we consider the corresponding rows for the cell B5 of the PivotTable (Emily, Green, Bike), this is just one row (the yellow one), and this row has the Bike value for the Transport attribute. When we apply the second filter, we have a single value for the attribute Transport, which is Car. At this point, the intersection between those two sets of Transport (one is only Bike, the other is only Car) is an empty set. Thus, the result for CarGreen measure is empty, because there are no corresponding rows for the selection made.

This can be tricky, but we finally have this behavior.

  • The CALCULATE function applies a calculation (the first parameter) for each cell, considering the resulting context by applying the filters (the second and following parameters) to the current context.
  • Each filter can have values for one or more columns.
  • Each column is computed individually in the filters expressions of the CALCULATE function
  • If a column value is specified in at least one filter, it replaces the selection of the current context for that column.
  • If a filter expression returns more columns, each one has its own independent set of values in the final calculation context definition
  • If a column is specified in more filters, the resulting values are the intersection of these set of values (for that column).
  • After all the filters have been evaluated, the intersection of the column values determines the calculation context for the expression passed as the first parameter to the CALCULATE function

Despite its complexity, this calculation is pretty fast. The key point is to understand all the side effects we have when a filter returns more columns than those we specified in the filter condition itself, which is something we have to consider carefully each time we use one or more FILTER functions inside a CALCULATE expression.

Published Sunday, January 03, 2010 9:39 AM by Marco Russo (SQLBI)
Filed under: , , ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Kasper de Jonge said:

Great post Marco, keep m comming :)

January 3, 2010 6:29 AM
 

SQLBI - Marco Russo said:

PowerPivot supports only one type of relationship between two tables, which is the one-to-many relationship.

February 9, 2010 9:44 AM
 

sam said:

Is there a way to add a measure that will show a 1 against the first instance of a particular name or name color combo

So in your example

Name   Flag

Emily    1

Kimberly 1

Emily    0

Victoria 1

Kimberly 0

Victoria 0

Emily    0

etc

February 13, 2010 10:17 AM
 

Marco Russo (SQLBI) said:

Sam,

the problem here is what is the definition of "first instance". Probably, you want to refer to the physical order of data. Can you be more explicit? Do we have some constraint to look for a solution?

Marco

March 18, 2010 6:07 PM
 

SQLBI - Marco Russo said:

When you use CALCULATE in DAX you are creating a new filter context for the calculation, based on the

May 26, 2010 7:30 AM
 

Martijn Muilwijk said:

Marco,

Is there any scenario you can think of in which you would prefer using the FILTER() statement over the CALCULATE() statement? In other words: It is always possible to achieve the same functionality with the help of CALCULATE() when using FILTER()

February 13, 2012 12:24 PM
 

Marco Russo (SQLBI) said:

Usually no, but FILTER has to be used when you have to pass a parameter to CALCULATE.

In fact:

CALCULATE( <expression>, <filter1>, ... <FilterN> )

every <filter> parameter is often the result (implicit or explicit) of a FILTER statement.

February 13, 2012 12:27 PM
 

Craig said:

Just want to say "thank you" for the great article after many hours of frustration..  with your help  ive finally worked out the filter function:) next step.. buying the book!

June 2, 2012 10:10 AM
 

Max said:

Hi Marco,

Can you suggest a clean way to use Calculate with a conditional filter. This was easy in MDX using SCOPE but I've not yet figured it out in DAX.

Say for example you want a total for every year except for the current year you want to use the previous year total.

MDX

CREATE MEMBER CURRENTCUBE.[Measures].[Best Estimate Claims] AS ([Measures].[Claims]), VISIBLE = 1  ;    

SCOPE ([Measures].[Best Estimate Claims],[Year Of Account].[Year Of Account].&[2012]);

THIS = ([Year Of Account].[Year Of Account].PrevMember,[Measures].[Claims]);

DAX

[Best Estimate Claims] = CALCULATE(SUM(DFR[Claims]), PARALLELPERIOD(YearOfAccount[FirstDayOfYear],IF(MIN(DFR[YOA])=2012,-1,0),YEAR),ALL(YearOfAccount))

(A relationaship exists between DFR[YOA] and YearOfAccount[YearOfAccount])

The two expressions above are equivalent in results but the DAX feels like a bodge

November 21, 2012 9:24 AM
 

Marco Russo (SQLBI) said:

You don't have something like SCOPE in DAX - you might write the same DAX expression in different ways, for example by moving the IF before the CALCULATE, but at the end it would not change that much.

MDX is more advanced, DAX is simpler (even if powerful) and has a different approach.

November 21, 2012 10:36 AM
 

Frank Tonsen said:

Hi Marco,

with regard to your book (pages 196-198)

maybe I am wrong, but IMO

CALCULATE(SUM(Orders[Quantity]),Orders[Color]="Green")

overrides color context

CALCULATE(SUM(Orders[Quantity]),VALUES(Orders[Color]),Orders[Color]= "Green")

keeps color context, or to be more precise kills color context and then restores it

CALCULATE(SUM(Orders[Quantity]), Orders, Orders[Color]="Green")

keeps all context, or to be more precise kills all context BUT THEN ALSO RESTORES IT.

THEREFORE, naturally the measure

CALCULATE(SUM(Orders[Quantity]),Orders[Color],Orders[Color]="Green" ,Orders[Size]="Small") returns no rows at all if a filter for 'Large' is defined in a slicer.

January 10, 2013 12:59 PM
 

Frank Tonsen said:

Unfortunately I made a typing error in the last paragraph, of course the second parameter of the CALCULATE function has to be Orders not Orders[Color]:

CALCULATE(SUM(Orders[Quantity]), Orders, Orders[Color]="Green" ,Orders[Size]="Small") returns no rows at all if a filter for 'Large' is defined in a slicer.

January 10, 2013 1:07 PM
 

Marco Russo (SQLBI) said:

Frank,

the parameters you put in a CALCULATE call are all put in an AND condition, so

CALCULATE(SUM(Orders[Quantity]),VALUES(Orders[Color]),Orders[Color]= "Green")

replaces existing filter on Color, but the two parameters will be put in AND together - so at the and the filter on Color has now values if the existing selection didn't include Green, or is just Green if existing selection included Green.

In case of

CALCULATE(SUM(Orders[Quantity]), Orders, Orders[Color]="Green")

you have the same result, but in this case Orders is like VALUES( Orders ), and gets all the rows selected in Orders instead of just a selection for one column - so Green filter always wins.

The last expression

CALCULATE(SUM(Orders[Quantity]), Orders, Orders[Color]="Green" ,Orders[Size]="Small")

returns no rows at all if you have a filter on Large, because the Orders parameter contains the current selection of Orders rows, which are only those Large (according to slicer selection) and the filter on Size equal to Small produces an empty result.

Sounds good?

January 10, 2013 1:24 PM
 

Frank Tonsen said:

Marco,

thanks for your quick answer. I guess it confirms my understanding.

Thus, regarding CALCULATE there is actually no point in using the facts table as a parameter because

CALCULATE(SUM(Orders[Quantity]), Orders, Orders[Color]="Green")

is the same as

CALCULATE(SUM(Orders[Quantity]), Orders[Color]="Green")

Right?

January 14, 2013 6:42 AM
 

Marco Russo (SQLBI) said:

No, they are not the same.

If you have

CALCULATE(SUM(Orders[Quantity]), Orders[Color]="Green")

and you had a selection of Color = "Red" in the PivotTable, such a selection is replaced for this formula with Green and you can evaluate a different set of rows in Orders.

When you have:

CALCULATE(SUM(Orders[Quantity]), Orders, Orders[Color]="Green")

then the initial selection is "copied" in Orders, and at this point the replacement of the Color selection with Green is still not enought to go outside of the current filter context, because the copy of Orders in the parameters of the CALCULATE makes a copy of the initial selection (i.e. Red) translated to the Orders table, and the result is an empty set in this case.

Marco

January 14, 2013 6:48 AM
 

Frank Tonsen said:

Marco,

now I got it.

Many thanks for your explanations.

January 14, 2013 7:44 AM
 

Gunshy said:

Hi,

Need help.

How can I conditionally calculate a table? for example, If Table[SourceType]="SALES" then SUM(Table[Quantity]) else CALCULATE(Table[Quantity],DATESBETWEEN(DateTable[Date],DATE(1900,1,1),LASTDATE(DateTable[Date]))) ?

Thanks

February 4, 2013 5:28 AM
 

Marco Russo (SQLBI) said:

Gunshy,

if you mean that you want to use the selection made by the user, you should use something like:

IF (

   HASONEVALUE ( Table[SourceType] ),

   IF (

       VALUES ( Table[SourceType] ) = "SALES",

       SUM( Table[Quantity] ),

       CALCULATE(

           Table[Quantity],

           DATESBETWEEN(

               DateTable[Date],

               DATE(1900,1,1),

               LASTDATE(DateTable[Date])

           )

       )

   )

)

February 4, 2013 5:53 AM
 

Oleg G said:

Wow! I just spent several hours re-creating this blog entry in Excel, and between this and last week's PowerPivot Workshop, I think I finally got it! Now it suddenly seems very easy and intuitive!

February 23, 2013 6:37 PM
 

Marco Russo (SQLBI) said:

After a few years, I still think it is not intuitive! But it can be easy after you get used to it. :-)

February 23, 2013 10:22 PM
 

Leshi said:

Hi Marco,

If I want to filter on multiple values such as Product ID in (123,456), how can I use IN statment in my filetr within CALCULATE measure...i.e. I am trying to acomplish something like this

CALCULATE(SUM(SALES),PRODUCT_ID=123,456) ...Is there an IN clause or something similar?

March 13, 2013 11:45 AM
 

Marco Russo (SQLBI) said:

Leshi, unfortunately there is no IN statement in DAX. If you store the list of products in another table, you might use the CONTAINS function in DAX.

March 13, 2013 4:36 PM
 

Leshi said:

Thanks Marco,

After tinkering around a little, I realized I could use an OR statement (||) if one to filter by few values instead...

Thanks for your feedback and keep up the good work

Leshi

March 13, 2013 4:42 PM
 

Marco Russo (SQLBI) said:

Oh yes, the OR statement is good too, it's just longer to write if you have many items to check! :)

March 13, 2013 4:44 PM
 

BI Analyst said:

Hi Marco,

Thank you for your tutorial.  I have a question regarding the example toward the end of this tutorial.  

In

CarGreen = CALCULATE( COUNTROWS('Demo'),

                     FILTER( 'Demo', 'Demo'[Color] = "Green"),  

                     'Demo'[Transport] = "Car" )

The measure returns no rows in the pivot table.

My understanding (or logic) is that because a transport cannot be a bike and a car at the same time.

But in your next example which you removed the Color filter

TransportCar = CALCULATE( COUNTROWS('Demo'),

                         'Demo'[Transport] = "Car" )

My logic is no longer holding because the measure column in the pivot table is not empty.

Could you help to explain (in the simplest way possible) why the one with Color filter return empty values and the one without Color filter return non-empty values?

Thanks again in advanced!

BI Analyst

April 8, 2013 1:48 PM
 

Henk Hofmans said:

I have got two tables with a commen key field. The tables cannot be related because there are duplicate values on both sides. In table "A" I create a new column that should count the number of occurences of "A.key" in the "key" column of table "B".

This is what I have tried, but doesn't work:

=calculate(COUNTA(A[Key]);B[Key]=A[Key])

How to solve this?

June 4, 2013 6:48 AM
 

Marco Russo (SQLBI) said:

You should write this:

= CALCULATE ( COUNTROWS ( A ), FILTER ( ALL ( A[key] ), A[key] = B[key] ) )

June 4, 2013 8:34 AM
 

Luke said:

Great post - very useful.

Does anyone know if there is a way of removing the current filter of a pivot table, but still keeping the row filters? For example, I'm trying to be able to filter a pivot table down on a particular group, but have one average across all the data with the same row splits.

Cheers

July 8, 2013 5:23 AM
 

Luke said:

Looks like the answer to my question is fairly straightforward - for ignoring the filter on a Pivot Table, but keeping the row/column filters you can just use CALCULATE(Expression,ALL(FilterColumn)

July 8, 2013 5:57 AM
 

Anand said:

Great Man. That is why I am Ur fan Marco.

I was trying to buy ur book on Data Modelling in Powerpivot for excel 2013.

But I was unable to get the e-Version.

February 13, 2014 12:27 AM
 

Marco Russo (SQLBI) said:

You can get the Kindle version on Amazon or other formats on oreilly web site. Let me know where you looked at.

February 13, 2014 12:36 AM
 

Rekha said:

Hi Marco,

I have 7 tables and each one is having unique column.e.g.I have table A with NRIC column as unique.Other table as B having Investigation ID as unique column with NRIC as a foreign key. All tables are some what related to each other.

When I try to create pivot table I come across the below scenario:

when I drap NRIC from table A and want to display related Investigation ID from table B, it shows me incorrect result.I mean one NRIC gets associated with all Investigation IDs.

I am new to Power Pivot Table.Please suggest how should I go about this.

Thanks

February 26, 2014 5:16 AM
 

Marco Russo (SQLBI) said:

Rekha,

from your description, it seems that you didn't create relationship between tables in the data model. Go in the Diagram View of the Power Pivot window and check that relationships are correctly defined between tables.

Marco

February 26, 2014 5:29 AM
 

Rekha said:

Thank for quick reply Marco.

I have created relationships between tables, but result is not as per our expectations.

For each NRIC from table A, it should return only those associated Investigation Ids from table B.

Am I missing anyhting?

February 26, 2014 5:48 AM
 

Marco Russo (SQLBI) said:

Rekha,

have you put a measure in the pivot table? If you don't have a measure, hierarchical relationships between different tables are not automatically identified by the Pivot Table in Excel.

Marco

February 26, 2014 5:50 AM
 

Rekha said:

Can you help me in this, as I am completely new to this?

Thanks

February 26, 2014 5:53 AM
 

Marco Russo (SQLBI) said:

Rekha,

I suggest you looking at the help or some presentation - look at how you create a pivot table starting from a model with more than one table. You can also look at some of our books:

http://www.sqlbi.com/books/

February 26, 2014 5:55 AM
 

Rekha said:

I was looking for some samples.

Thank you so much Marco for your help.

February 26, 2014 5:59 AM
 

Malcolm said:

Can calculate function together with a Contain function. eg.  Calculate(sum(table1),contain(table1,col1,"ast_")).

I have purchased the the book Microsoft Excel 2013 - Building Data Models with PowerPivot written by you and couldnt find a solution in that

Thanks

March 3, 2014 8:17 AM
 

Marco Russo (SQLBI) said:

It's not clear what you want to do. Contains is a sort of "lookup", which might be not what you want to do - it seems that you want to do just this:

CALCULATE ( SUM ( table1[col2] ), table1[col1] = "ast_" )

But if you want to do a sort of "like" statement, then you have to write:

CALCULATE ( SUM ( table1[col2] ), SEARCH ( "ast*", table1[col1], 1, 0 ) > 0 )

Check this article for more info about string comparison in DAX:

http://www.sqlbi.com/articles/from-sql-to-dax-string-comparison

March 3, 2014 8:37 AM
 

Malcolm said:

Yes I wanted a like statement and got hold of your thread after I left a comment. But still could'nt solve it. Figured out the reason to be the * mark. Thanks Marco.

March 4, 2014 9:19 AM
 

Rohit said:

Hi,

I have a table with 10 columns. One of the column has values. I want to add another column where it will add the values with a filter condition on another column. EX: i have 10 rows and 2 columns, one having numerical values and other dimension data as shown below

x  |  y   |  New Column(Get values from x column where Y = 'A')

1  | A    |  1

2  | A    |  1

3  | A    |  1

4  | B    |  0

5  | B    |  0

6  | B    |  0

7  | C    |  0

8  | C    |  0

9  | A    |  1

10 | A    |  1

Can anyone help me understanding how to apply the filter and get the values in DAX.

Thanks in advance.

May 16, 2014 12:55 PM
 

Marco Russo (SQLBI) said:

Rohit, it's not clear whether you want to obtain this in a PivotTable or in a Power Pivot table. For the latter, just create a calculated column with the expression [x] = "A"

For a PivotTable you can create a measure defined with

IF ( HASONEVALUE ( table[x] ), table[x] = "A" )

May 17, 2014 12:54 AM
 

ramiro said:

marco, i have a problem, i have two measures, and then i made a calculation substraction, and i get positive numbers and negative numbers, with this result i want only sum negative numbers but i dont know what formula type, you can helpme??

I appreciate anything help.

I talk about power pivot.

May 20, 2014 7:21 PM
 

Marco Russo (SQLBI) said:

Ramiro, sorry, I don't understand the question...

May 21, 2014 1:35 AM
 

ramiro said:

Marco, sorry.

I put the example:

Product           Sales    Budget      Dif

A                 1         1           0

B                 2         2           0

C                 1         0           1

D                 3         5          -2  

Total             7         8          -1

I need separate the negative from Dif and then sum it. But the result i get on Total is always the difference not the sum.

Product           Sales    Budget      Dif    Only negatives

A                 1         1           0          0

B                 2         2           0          0

C                 1         0           1          0

D                 3         5          -2         -2

Total             7         8          -1         -1

Only negatives, i calculate: =IF(Dif<0,Dif,0) But on the Total always give me de the difference not the sum. That i want is sum all negatives number only when i separate on column Only negatives.

I hope you understand me now, I am from Mexico and I speak English a Little.

Thanks a lot, anyway.

May 21, 2014 9:25 AM
 

Marco Russo (SQLBI) said:

Create a measure such as:

CALCULATE ( SUM ( table[dif] ), table[dif] < 0 )

Marco

May 21, 2014 10:04 AM
 

ramiro said:

I forgot, but (Sales) and (Budget) are already a measures, and (Dif) is a measure of (Sales) minus (Budget) and (Only negatives) is a measures for (Dif).

If I do what you say, this appear:

Error de cálculo en la medida 'INVENTORY_TRANS'[A]: Se ha usado una función 'CALCULATE' en una expresión True/False que se utiliza como expresión de filtro de tabla, lo que no está permitido.

May 21, 2014 12:04 PM
 

ramiro said:

OH! SORRY, THANKS FOR THE ANSWER.

May 21, 2014 12:08 PM
 

Anand said:

Marco , any Idea How Can I change "CarGreen" to "CarRed" ie

"Car"& AnyVariable so that I can Dynamically change the column

Name.

At last Once again for proving an Excellent book on PowerPivot for

SQL & VBA coders like us.

June 27, 2014 2:12 AM
 

Marco Russo (SQLBI) said:

Anand, you cannot dynamically rename column names. However, I would suggest you having a column "Car" and Green / Red / ... as values in that column. Then, you can create a measure and split it by values in Car attribute.

June 27, 2014 1:01 PM
 

Kumar said:

Marco, I have one problem in one of measure creation in Tabular model. I have to create a  measure which should display value for only monthly. It should not give null value for Yearly, Quarterly. I have a Date Dimension which has Year, Quarter and Month values. How can I achieve this

July 8, 2014 7:53 PM
 

Marco Russo (SQLBI) said:

Kumar, take a look at the ISFILTERED function to check what the user is selecting. I'm not sure I understand what you want to obtain when Year and Quarter are selected.

July 9, 2014 1:16 AM
 

Kumar said:

You’re Champ Marco, ISFILTERED function worked to me. This is unique business scenario, where we have to show the data for monthly rest all we need to show blank value. Thanks for your suggestion.

July 9, 2014 2:37 PM
 

Ron Jonas said:

Marco - I'm hoping you can help... This question pertains to "Calculated Items" or what used to be calculated items before PowerPivot....  I have the need to calculate the difference between items within the same data column.  To try and keep it simple, let's say I have 3 columns of data; Component, Version and Amount.  We'll use Bookings as the Component item, Archive and Active as Version items and amounts.  What I want to do is to calculate the difference between Archive and Active within the Bookings component.

The data table would show:

Component         Version        Amount

Bookings          Archive        1000

Bookings          Active          950

In my pivot, I want to show the Archive and Active amounts and have the pivot table calculate the difference... When I try and use calculate(sum), it doesn't like it... :-)

Any thoughts?

Much appreciated.  Ron

July 11, 2014 6:36 PM
 

Marco Russo (SQLBI) said:

Ron, you should create a measure (calculated field in Excel 2013) by using simply these expressions:

ArchiveAmount := CALCULATE ( SUM ( table[amount] ), table[version] = "Archive" )

ActiveAmount := CALCULATE ( SUM ( table[amount] ), table[version] = "Active" )

DifferenceAmount := [ArchiveAmount] - [ActiveAmount]

July 13, 2014 4:40 AM
 

Andy said:

Hi,

so, do I understand correctly that there is no easy way of calculating the GRAND TOTAL by using a formula which takes existing filters into consideration?

Every pivot table is giving me a GRAND TOTAL at the bottom based on various filters (on slicers or the pivotfilter). But some smart DAX developer guy is not able to program an expression that returns the same value? I am thinking very easy like =GRANDTOTAL([column1]) :)

If I change a filter, I always have to change my measure manually?

I am searching for days now and I am refusing to believe that there is no easy and flexible way.

Cheers,

Andy

July 14, 2014 5:46 PM
 

Marco Russo (SQLBI) said:

Andy, I don't understand what you mean. When you define a measure, it's automatically calculated in the Grand Total by the Pivot Table in Excel, which generates an MDX query.

If you need to obtain the grand total in a pure DAX query (assuming you're directly querying the model without using a pivot table), you should use ROLLUP in the SUMMARIZE statement.

July 15, 2014 2:04 AM
 

Ron Jonas said:

Marco - You.ROCK...  Thank you so much... It worked like a charm!!

July 15, 2014 5:40 PM
 

Andy said:

Hi,

My target is to create a measure TotalNetSales per each year.

I have the following PowerPivot and with the DAX:

=CALCULATE(SUM(Products[NetSales]),ALLSELECTED())

I get this:

2013 2014

NetSales GP TotalNS NetSales GP TotalNS

$100.00 $50.00 $7,800.00 $200.00 $50.00 $7,800.00

$1,000.00 $300.00 $7,800.00 $2,000.00 $300.00 $7,800.00

$200.00 $60.00 $7,800.00 $400.00 $60.00 $7,800.00

$300.00 $70.00 $7,800.00 $300.00 $70.00 $7,800.00

$400.00 $40.00 $7,800.00 $500.00 $40.00 $7,800.00

$500.00 $30.00 $7,800.00 $700.00 $30.00 $7,800.00

$600.00 $20.00 $7,800.00 $600.00 $20.00 $7,800.00

$3,100.00 $570.00 $7,800.00 $4,700.00 $570.00 $7,800.00

But what I want is this:

2013 2014

NetSales GP TotalNS NetSales GP TotalNS

$100.00 $50.00 $3,100.00 $200.00 $50.00 $4,700.00

$1,000.00 $300.00 $3,100.00 $2,000.00 $300.00 $4,700.00

$200.00 $60.00 $3,100.00 $400.00 $60.00 $4,700.00

$300.00 $70.00 $3,100.00 $300.00 $70.00 $4,700.00

$400.00 $40.00 $3,100.00 $500.00 $40.00 $4,700.00

$500.00 $30.00 $3,100.00 $700.00 $30.00 $4,700.00

$600.00 $20.00 $3,100.00 $600.00 $20.00 $4,700.00

$3,100.00 $570.00 $3,100.00 $4,700.00 $570.00 $4,700.00

I have tried to do it with a SUMMARIZE statement and ROLLUP as you suggested, but I cant get it to work.

Thanks for any help,

Andy

July 28, 2014 10:33 PM
 

Andy said:

oops, that doesnt look good anymore :)

The difference between these Pivots is, that for the first Total it gives me the sum of both TotalNS for year 2013 + 2014 (3100 + 4700 = 7800). But I need the TotalSum for each year separately.

Shame I cant insert a picture.

Sorry again. Andy

July 28, 2014 10:49 PM
 

ZT said:

Hi Marco

I get one row for every Name when I write the code from your exampel in Powerpivot 2013

CarGreen = CALCULATE( COUNTROWS('Demo'),

                     FILTER( 'Demo', 'Demo'[Color] = "Green"),  

                     'Demo'[Transport] = "Car" )

Or

CarGreen = CALCULATE(

              COUNTROWS('Demo'),

              FILTER( 'Demo', 'Demo'[Color] = "Green"),  

              FILTER( ALL( 'Demo'[Transport] ), 'Demo'[Transport] = "Car" ) )

Why is that?

October 3, 2014 4:34 PM
 

ZT said:

Ah I see now.. I didnt applied the filter on the slicer.

October 3, 2014 4:41 PM

Leave a Comment

(required) 
(required) 
Submit

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog

Syndication

Archives

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement