**A Power BI Technique Mined from the Power Pivot Archives**

**Below, you will find one of our all-time favorite Power BI techniques. ** In the ten (yes ten) years we’ve been operating this site, we’ve written over 1,000 articles on Power BI. Hard to imagine - even for us - but true.

**Years ago, we first wrote up this technique in the context of Power Pivot** – Power BI’s cousin and predecessor, which we like to call “Power BI in Excel.”

**Since the two products share the same brains (DAX and M), this technique is more relevant today than ever.** Everything below is 100% legit for Power BI – the same data model “shape,” the same DAX, etc. – but now you can use it to power up your Power BI visuals, and not just PivotTables. Enjoy!

“No [doubt] she’ll freak. I’m just contemplating the =IF()’s…”

-Marcellus Wallace, obvious master of the spreadsheet arts

## CALCULATE is a supercharged SUMIF

I can’t believe I didn’t say this last time: =CALCULATE() is a lot like =SUMIF(), which is a function that Excel gurus know and love… and sometimes hate 🙂 SUMIF and its cousins like COUNTIF and the plural SUMIFS are often indispensable. When you want to perform an aggregation on a table, but just include rows that meet a certain criteria, the SUMIF family is often where you turn.

But SUMIF has a few limitations. First of all, the conditional syntax is kinda awkward. Second, if you want an aggregation that is not covered by the functions provided, you are out of luck – there is no MAXIF, for instance. And you cannot use any of these functions inside a PivotTable, which, when you think about it, would be one of the most useful places to employ them.

=CALCULATE() fixes all of those limitations, and then does things you wouldn’t think to ask for 🙂

### The syntax of CALCULATE()

=CALCULATE**(**<aggregate expression>, <filter1>, <filter2>, … **)**

**<aggregate expression>**

This is basically anything that would itself define a measure. The following are all legal examples:

- SUM([Column])
- SUM([Column1]) / MAX([Column2])
- The name of another measure that’s already been defined

Pretty cool huh? Literally you can CALCULATE on any aggregate expression you can dream up – even another measure that you defined before, like my “Avg Sales per Day” measure from the temperature mashup demo.

**<filter1>, <filter2>, …**

And then you can conditionally evaluate that aggregate expression based on any number of filters you’d like to apply.

Valid examples:

- [ColumnName] = “Foo”
- [ColumnName] >= 6
- ALL([ColumnName])

Which is to say, that the syntax is exactly what you’d expect it to be 🙂

### The power of ALL() is truly revolutionary

That ALL() thing is pretty unexpected though – it lets you create measures like “All-Time Sales” – if you set ALL([Date]) for instance, the resulting measure will respect all of the filters in the pivot table… but not any filters on Date, meaning that even in a pivot sliced to Year = 2009, you could still see a measure that showed Sales for all years combined. Useful in some cases for sure.

Of course, you can also create a CALCULATE expression that employs ALL() as a filter, then use that CALCULATE as the denominator of a measure. Something like:

=SUM(SalesTable[Sales]) /

CALCULATE**(**SUM(SalesTable[Sales]), ALL(SalesTable[Sales])**)**

Would give you a measure like “Percentage of All-Time Sales.”

ALL() warrants its own post, and perhaps multiple posts, so I will revisit this later.

But in the meantime, back to football 🙂

So now you know that CALCULATE is a supercharged SUMIF. If you liked this post and want to read more about CALCULATE, here’s a great next post: https://powerpivotpro.com/2014/03/becoming-one-with-calculate/. If that post was too human and you want to read a description of CALCULATE written for robots, please go here: https://msdn.microsoft.com/en-us/library/ee634825.aspx

[…] CALCULATE – the big Kahuna. A version of SUMIFS that I can use in a pivot measure to remove, alter, or override pivot filters and just aggregate the source rows that I want. […]

[…] (If you need a refresher on CALCULATE, which is probably the most useful function in all of DAX, check out this post.) […]

[…] than standalone Excel, and it will actually seem easier than normal Excel. Check out the CALCULATE function and you will see what I […]

Nice and educational

[…] CALCULATE, ALL, and maybe even SUMX (in that order!) before digging into the DAX chapters. The book […]

[…] You can learn more about CALCULATE here, here, and here. […]

As an MDXer, I wouldn’t say that ALL() is revolutionary in terms of functionality, but it is revolutionary in the regard that it allows mere mortals to do MDX style calculations. Power Pivot in general is great, because the less data cubes I have to make for quick exploratory projects, the better.

Agreed, it is revolutionary from an Excel standpoint, which means it’s revolutionary to millions 🙂

But yes, not to MDX pros such as yourself 🙂

how do you write Calculate if you have a condition for example all sales in month of december?

How to be a calculated column

SELECT SUM (value) FROM table GROUP BY column

Alakazaam-infromtiaon found, problem solved, thanks!

sir i want to know next day adj closing of dax plz tell me simple calculation

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

I’m not sure about Table[SourceType] and whether that’s something special or just a regular column, but in terms of doing an IF(), try this:

https://powerpivotpro.com/2011/03/the-magic-of-ifvalues/

The Table[SourceType] identifies the record if it is a Sales entry or an Inventory record. each type of record must be calculated differently.

Here is the sample data:

CustomerCode Date Quantity SourceType

STORE A 1/1/2012 100 SALES

STORE A 1/1/2012 50 INVTY

STORE B 1/2/2012 100 INVTY

STORE A 1/3/2012 -100 INVTY

STORE A 1/3/2012 100 SALES

OK I think I would write this as two measures:

M1 = CALCULATE(SUM(Table[Qty]), Table[Type]=”SALES”)

M2 = CALCULATE(SUM(Table[Qty]), Table[Type]=”INV”, DATESBETWEEN(…)

Then you could use the measures separately. Or if for some reason you wanted the two recombined into one measure, you could write a third measure:

M3 = [M1] + [M2]

I don’t know why it would make sense to add Sales to Inventory (probably doesn’t), but just in case, that’s how I would do it.

In the original structure of the data, sales and inventory data are in different table. But since that the Power View Map have only one measure, I need both entry types into one table.

do you have any suggestions on how I can achieve this?

Thanks.

Hi I would like to set up a measure that allows me to calculate moving annual sales. Which formula should I use?

Hi there AK! Type “moving average” into the search box here on the site and you will find a couple starting points I think 🙂

Hugely helpful post. Thank you very much.

How would this formula change if you wanted to sum column A if column B was “cats” OR “dogs” for example. Is there any way to do this without creating Measure 1 and Measure 2, and then combining the two?

-Eric

The || operator is what you need.

Table[column] = “blue” || Table[column] = “red”

You are a wonderful human being. Thank you. I”l let you know if I have any issues. Thanks again.

alright, so I have one, what if I have a long list of work centers, and I want to sum those up individually by a date? I was thinking it could be done by adding a slicer to a cube set, but do not think that works…

Michael,

Wouldn’t you just put the Work Centers on the rows in your pivot table, then feed the date as a filter to CALCULATE? Or is the problem that you want the date to be something that the user can choose?

Tim

something they can choose as well. I can pull the data apart when turning the pivot table into calcs, then putting all the filters in with a time slider, but would still like to add a calc field within PP to pull data like a sumifs formula, where instead of stating the actual (= “this”, or “this”) then calc, but rather if (thier choice) and (thier date choice) would gve the result.

Michael,

i have a problem, i made two measures, sales and budget and then made a substraction, then i get values positives and negatives, after that i want to sum only the negative values, but i cant. Each line is fine, but at the moment of the total sum, the result is wrong because give me the diference between totals. How i can solve this problem, I apreciate any help. Thanks.

You may want to try and use the data from the PowerPivot into a cube formula and then put those formulas into a table to resolve. Once you have the data into a pivot table (use a limited amount because you can calc in the rest), change the pivot table to calcs (convert), then add the sales and budget in with CUBEMEMBER and CUBEVALUE into a table like report.

Hi,

sorry to tag onto an old thread. I’ve searched the site (and others) and can’t seem to find a solution.

In my table I have hospital activity. I can see on one row that patient X had an emergency admission (defined by a value in a different column for that row) on a certain date. I can also see on a different row that the same patient X on that same date had an activity in an inpatient department (ie their emergency activity is one row, and the resultant treatment is in another row). What I would like to do is create a calculated column that says, if this row is an emergency admission then return the resultant department code – which can only be matched by patient ID and admit date.

What I need is something that says:

if AE_disposal=1 [this means emergency admission]

find Specialty_code by ID and DATE

where ID and DATE match ID and DATE of AE_disposal=1

thanks

Mejd

Hi there

I’m exploring Powerpivot DAX formulae and its ability to do the equivalent of array formulae in Excel using dynamic/cell based criteria.

I’d like to do the equivalent of

1. SUMIFS

2. AVERAGEIFS

3. COUNTIFS.

I’ve scanned through previous posts, but cant seem to find the syntax that you would you use for the calculated columns in a Powerpivot Data Model?

i.e – what is the syntax when the criteria changes with each row in the table?

Example:

Column1 Column2 SUMIF using COL1 as Criteria AVERAGEIF using Col1 as criteria

A 2 3 1.5

B 3 6 3

C 4 8 4

A 1 3 1.5

B 3 6 3

C 4 8 4

https://powerpivotpro.com/2013/07/writing-a-subtotal-calc-column-aka-the-simplest-use-of-the-earlier-function/

🙂

I am experiencing hard problems with simple CALCULATE function.

I dont understand what is wrong with it. Struck on this problem for 5 hours already, and cant find solution. Even, i dont know the problem…

Here the problem picture – http://s1.postimg.org/w036u7h73/problem.jpg

And here the sample of the example – https://drive.google.com/file/d/0B7oBaHlCIEhadU5pdGtOem1ETlU/view?usp=sharing

PLease, tell me what am i doing wrong??

I so much nervoius right now… =(

Hi guys,

I like the blog. Please can you update the article so that where you have “filter1″,”filter2”, etc, that you make clear that these use the FILTER function? On Excel 2010, using CALCULATE without a FILTER function doesn’t work.

Doesn’t work:

=CALCULATE(SUM([Something]),[ColumnName] = “Foo”

Does work:

=CALCULATE(SUM([SomethingOnTableOne],FILTER([TableOne],[SomethingElseOnTableOne]= “Foo”))

Zambino, I can attest that it ABSOLUTELY works in Excel 2010. We run training using both Excel 2010 and Excel 2013 and I have demoe’ed that specific usage numerous times using Excel 2010. There is something else going on. Your best best may be to post a question on http://forum.powerpivotpro.com/forums

Hello,

Is there any expert who can change this normal formula to DAX formula.

=AVERAGEIFS([ValuesToAvg],[RouteCol],$A19,[DatesCol],”>=” & H19-30,[DatesCol], “<" & H19 )

$A19 is the one route which changes when dragged downwards.

$H19 is date relative date for each row which changes when dragged down.

Actually I want moving average of past 30 days for each route seperately(a column having different routes).

Please help.

Thanks in advance.

in continuation of the above post

here is my file

https://drive.google.com/file/d/0Bzt…ew?usp=sharing

I can’t tell you how many times a day I am hitting this site for guidance. This is the first time I haven’t been able to find an answer.

I have filtered results in a power pivot table that not only do I need to compare, but I need to be able to use the comparison in later calucluted measures. For example, I send a catalog to a list and withhold a control group to later calculate how much “lift” the catalog provided above what would have happened if nothing was done. In a single column (“[Control_Group]”), each recipient is flagged if they are in the control group (“control”). Recipients not in the control have a blank value in the control group column.

DAX formulas makes it easy to summarize response rate, revenue, AOV, etc. for each group. What I am not able to do is find a DAX formula that can calculate the lift; that is, the difference in the amount of revenue each group generated per member. Revenue per member, yes. But not the difference. At least not as a calculdated measure that allows me to use it in additional summary calculations.

The pivot table would look like this:

Catalog Count Orders Revenue AOV Revenue_per_person Lift* Total_Lift* Cost_of_Catalog Net_Profit*

Blank 1000 90 9000 100 9 1 1000 600 400

control 100 10 800 80 8 0 0 0 800

Each asterisked measure relies on being able to reference the difference between the values under Revenue_per_person.

I have tried something like this: Lift:=Revenue_per_person – CALCULATE(Revenue/Count,ALL(Customer[Control_Group],FILTER(Customer,Customer[Control_Group]=”control”))

But row context in the “Blank” row already has no rows with “control group” in the [Control_Group] column, so the result is zero.

Instead of the ALL command, what I need is the ability to ignore that one row context (“Blank()”) and substitute it with another row context (“control group”). How can I do that?

Or do I need to take another approach?

Hi Dean,

I think that you are close. I think maybe break up what you are trying to do into smaller steps.

You can try this and see if it gives you the results you need…

I’m using “Customer” as the table name. I am using “Catalog” as the Control/Not Control field name. “Revenue” as the revenue and “Count” as the count (I am trying to use the same names for the table/columns as you provided in your example).

[Revenue_For_Only_Control] = CALCULATE(SUM(‘Customer'[Revenue]), ALL(‘Customer'[Revenue] ), ‘Customer'[Catalog] = “Control” )

This should get the total Revenue ($800) for the Control population for both the Control & Non-Control row breakout in the pivot table.

Then you could do the same thing for the Count measure.

[Count_For_Only_Control] =CALCULATE(COUNTROWS(‘Customer’),ALL( ‘Customer'[Count] ), ‘Customer'[Catalog] = “Control” )

This should get the total Count (100) for the Control population for both the Control & Non-Control row breakout in the pivot table.

Do the calculation of ($800/100) by using the DIVIDE formula to the (8) value.

[Avg_Cust_Rev_Only_Control] = DIVIDE(Revenue_For_Only_Control,Count_For_Only_Control)

And then get the final Lift calculation by doing the subtraction.

I hope that helps!

Thanks, Krissy. I’ll give it a try.

Hi Rob,

For using CACLCULATE FUNCTION, is it mandatory that Tables should be related?

CustomerCode Date Balance

STORE A 1/1/2017 100

STORE A 1/2/2017 50

STORE A 1/3/2017 90

.

.

.

STORE A 21/4/2017 80

STORE B 1/1/2017 150

STORE B 1/3/2017 90

STORE B 1/3/2017 -60

.

.

STORE B 21/4/2017 100

I have trouble ranking my data as it is slightly more complicated than the described one. can you please assist with this?

I have 4000 different stores and record daily their sales balance with a lag of one day.

I need to create the following structure in a dashboard so that to display daily balances and changes for the top-20 of them and for all the rest:

– today vs yesterday

– today vs the same day 1, 2, 3 months ago

– today vs the last day of previous month (MTD), today vs. end of last trading day of previous year(YTD)

Ideally, I’d like one to be able to dynamicall set the base date and then get the above data.

In addition to that I’d like to be able to calculate the average and media values over a period (month, quarter, ytd)

Any effort to use the rankx with filter it does produce an error

any help would be appreciated.

thank you!

in excel i can countif value in one column appears in another column. I have Distinct column and i have duplicate column, i want to count how many times the distinct column value appear in duplicate value column, in same table…and i want the results to rollup in column for distinct values…..can anyone help?

how can i count distinct values from column that’s in another column….example distinct column has 123456 rows (distinct), column 2 has 223366…i would to show a rollup/total count of column2 in a column based on column 1. example 1 =0 , 2=2,3=2,4=0,5=0,6=2….and it weekly file…..can anyone assist?

count values in one column, based on values in another column

I usual make quite simple dax formulas with connected tables.

This time, need some help to compare 2 non-connected tables

table1 :

columns = regnr; code; begindate; enddate

table2:

columns = regnr; date

for table2 I added an calculated column to find the correct code.

It should compare (in between) begindate & enddate of table1 with date of table2

The answer must be something like:

=calculate( values(table1[code]);filter(table1;table1[begindate]=[date] && table1[regnr]=[regnr]))

But it’s not correct. What’s wrong ?