DAX / Powerpivot: how to do a "IF Parent of pivot row >x"

Question

Searching for a way to do the following; any help is greatly appreciated!

Setup: Excel 2013 & Powerpivot;

Pivot in Values:

Value #1 and #2 Sum of numeric values

Calculated field in Value #3 (% of Value #1)

Calculated field in Value #4 (Value #3 - Value#2)

Value #5 and #6 Sum of numeric values (different than #1 and #2)

Calculated field in Value #7 (% of Value #5)

Calculated field in Value #8 (Value #7 - Value#6)

Rows in Pivot

Row#1 Area

Row#2 District

Row#3 Cluster ID

Trying to create new calculated field that will do the following (showing via an excel formula to describe)

If(and(District Value#4)>0,ClusterID Value#8>0),1,2)

Any idea how this can be done in DAX / Powerpivot ?????

I have tried Hierarchies, but no luck.

Calculated formulas will allow me to see the pivot row name, so maybe a filter, but cant seem to get the syntax correct, assuming it is even allowed...

enter image description here Stuck..

All suggestions are very much appreciated !

This question isn't off topic. It's asking for a DAX formula. We have DAX questions all the time, hence the availability of the tag. It's no different than asking a SQL question. Power Pivot is like a local instance of SSAS tabular. — mmarie
DAX and PowerPivot are on-topic for this site. One is a programming language and the other is the data source. Just because it's Excel doesn't mean it's a SuperUser type of question. — billinkc

Petr Havlik Petr Havlik · Accepted Answer · 2013-12-06T19:17:28

PowerPivot has some pretty powerful features for adjusting its calculations to its context (rows).

If there is anything specific I want to do with just the row parents (= totals), function HASONEVALUE() is the way to go.

IF(HASONEVALUE(Stores[StoreName]), [Units Sold], BLANK())

This command will calculate expression Units Sold for all the rows but row parents. You might need to switch it, but generally this should be an easiest and most reliable way how to identify row parents.

I couldn't understand your question and wasn't sure about the calculations you are doing, so I apologize for not giving a more detailed response... Hope this will help.

Also, see this brilliant article by Rob Collie.

DAX / Powerpivot: how to do a "IF Parent of pivot row >x"

1 Answers