simulating drill thru without totals in one column

Question

• Hi.  My pp shows plant, area and distribution channel in row labels.  It shows original order qty, quantity actually shipped and total invoice amount (qty shipped x price) in column values.

As I make it more interesting, I'm wondering if I can have a price column in pp that 1st of all never sums, and secondly only shows once i add enough additional levels under channel to finally show the most detailed shipment record?  I'd probably add customer underneath channel row label etc etc and even include a shipment primary key (hopefully that can be hidden) to get the detail I'm looking for.

Right now it looks like this:

Row Label                         Original Qty            Shipped                Amt

Plant A                              xxxxxxxxxx            xxxxxxxxxxxxx     \$xxxxxxxxxxxxxxx

Plant Area Z                     xxxxxxxxxx            xxxxxxxxxxxxx     \$xxxxxxxxxxxxxxx

Channel 1                    xxxxxxxxxxx           xxxxxxxxxxxxx     \$xxxxxxxxxxxxxxx

Channel 2                    xxxxxxxxxxx           xxxxxxxxxxxxx     \$xxxxxxxxxxxxxxxx...

Wednesday, January 29, 2014 4:45 PM

• Is this what you are trying to achieve?

If so then you can create the following calculated fields:

DrillPriceNew:=
IF(
ISFILTERED(sales[InvoiceNumber]),
CALCULATE(SUM(sales[price])),
Blank()
)

DrillExtendedPrice:=
IF(
ISFILTERED(sales[InvoiceNumber]),
CALCULATE(SUM(sales[ExtendedPrice])),
Blank()
)

Hope this helps,

Michael

EDIT: This solution assumes that InvoiceNumber is unique but if it's not and you have something like a line number then you can concatenate these together in a calculated column and then replace the column reference in the ISFILTERED() function with this new calculated column.

• Edited by Wednesday, February 12, 2014 9:24 AM small addition
• Marked as answer by Wednesday, February 12, 2014 1:36 PM
Wednesday, February 12, 2014 9:09 AM
• To help you to get a better understanding of evaluation contexts in DAX and the HASONEVALUE function, I recommend reading the following two official TechNet articles in the order listed:

Context in DAX Formulas

http://technet.microsoft.com/en-us/library/gg413423.aspx

HASONEVALUE Function (DAX)

http://technet.microsoft.com/en-us/library/gg492190.aspx

EDIT: The main issue you have been facing with the HASONEVALUE function is due to using it in a calculated column instead of a calculated field (measure)

The following two articles may help to clarify the difference between the two:

Calculated Fields in Power Pivot

http://office.microsoft.com/en-gb/excel-help/calculated-fields-in-power-pivot-HA102837191.aspx

When to Use Measures vs. Calc Columns

http://www.powerpivotpro.com/2013/02/when-to-use-measures-vs-calc-columns/

• Edited by Wednesday, February 12, 2014 10:07 AM additional info added
• Marked as answer by Wednesday, February 12, 2014 1:36 PM
Wednesday, February 12, 2014 9:34 AM

All replies

• Hi,

What should help to solve your problem is the function hasonevalue see http://technet.microsoft.com/en-us/library/gg492190.aspx for Details.

Your Price measure could look something like

PriceOnlyOnShipmentLevel := IF(HasOneValue(Table[Shipment Record ID]);[PriceMeasure];Blank())

Fields can be hidden in the powerpivot window (hide from Client Tools).

Is that what you were thinking of?

Regards,

Julian

Wednesday, January 29, 2014 5:38 PM
• I think so but will have to try it.  It will all depend on what HasOneValue means.  If by some chance all the detail in a certain part of the "hierarchy" of row labels has the same price and pp decides to sum in that case, I'm kind of back at square one.  I will post back here after I've read the doc at your link.

If this function can take hasonevalue on pk but apply the rule to the price that would be awesome.  I cannot tell from your DAX if that is what your are suggesting.

• Edited by Wednesday, January 29, 2014 6:00 PM afterthought
Wednesday, January 29, 2014 5:57 PM
• i dont think this is working unless the context in the powerpivot window's tab for this table acts differently than what I'll see in the pivot table once I add my row label levels.

My expression is shown below and I'm seeing all blanks in the powerpivot window's new column .  I verified that pk is unique, number of rows is the same number expected, unit_price is present etc etc in this fact table's pp window tab.

=if(HASONEVALUE(FactShipment[pk_factShipment]),[unit_price],Blank())

Wednesday, January 29, 2014 7:05 PM
• you may add a calculated column for your price calculation as MyPrice = [invoice amount] / [qty shipped]

then you can use it in your final measure:

=IF(HASONEVALUE(FactShipment[MyPrice]), FactShipment[MyPrice])

this will show the price on detail level and also on upper levels as long as all details have the same price

hth,
gerhard

Gerhard Brueckl
blogging @ http://blog.gbrueckl.at
working @ http://www.pmOne.com

Wednesday, January 29, 2014 7:19 PM
• thx Gerhard but I'm thinking I'd better get Julian's solution to work.  I dont want price showing at levels higher than the lowest level detail.

I added my pk to the pivot table (tried as a row label and then separately as a column value) and still the price column shows blanks always.   I did this after adding customer, item and invoice # under channel as additional row label levels.

And it looks like its going to have to work in the power pivot window because that's where hiding cols seems to be controlled.  I dont see any way to hide a column in the pivot table independently of the pp window.

Wednesday, January 29, 2014 7:47 PM
• Hi Gerhard.  I'm thinking a bit more about your idea as an average price.  How would you prevent pp from summing all detail as opposed to using the aggregated values (total \$ / total qty) to do the division?
Wednesday, January 29, 2014 8:44 PM
• well, the easiest thing to do then is the following:

MyPrice:=IF(COUNTROWS(FactShipment) = 1, [invoice amount] / [qty shipped])

only if your slice/selection contains one single factrow the price is calculated, otherwise its null (default if no false-part is defined)

Gerhard Brueckl
blogging @ http://blog.gbrueckl.at
working @ http://www.pmOne.com

Wednesday, January 29, 2014 11:17 PM
• thx.  if that is DAX, it looks like you can just replace the division with unit price.  Is this an alternative to hasonevalue?   I wonder if this will work where hasonevalue didnt?

Thursday, January 30, 2014 2:09 PM
• the difference is that if you have 3 rows with the same unitprice in your current slice then COUNTROWS()=1 will return false and display BLANK() whereas HASONEVALUE() would return true and display that unitprice

Gerhard Brueckl
blogging @ http://blog.gbrueckl.at
working @ http://www.pmOne.com

Thursday, January 30, 2014 3:38 PM
• thx.  Are you saying that in your proposal countrows() depends on the value of invoice_amount/qty_shipped being unique? Across all rows aggregated and detail?

Maybe my best strategy is to just ask the community if it can assist in explaining why hasonevalue isnt working for me.

Thursday, January 30, 2014 4:26 PM
• ok, take this example, it should explain the difference

 Plant Plant Area OriginalQty Amount Price A Area 1 100 250 2,5 B Area 1 200 500 2,5 B Area 1 300 750 2,5 C Area 2 10 25 2,5

 Row Labels wHASONEVALUE wCOUNTROWS Area 1 2,5 A 2,5 2,5 B 2,5 Area 2 2,5 2,5 C 2,5 2,5 Grand Total 2,5

wHASONEVALUE:=IF(HASONEVALUE(FactShipment[Price]),  VALUES(FactShipment[Price]))
wCOUNTROWS:=IF(COUNTROWS(FactShipment) = 1, VALUES(FactShipment[Price]))

HASONEVALUE refers to unique values in a column and returns true if only one unique value exists
COUNTROWS on the other hand counts the fact-rows, regardless of unique values

Gerhard Brueckl
blogging @ http://blog.gbrueckl.at
working @ http://www.pmOne.com

Thursday, January 30, 2014 5:32 PM
• ok.  My DAX is in the pp window and coded for an extra calculated price column.

Is hasonevalue in the context of the row label when looking at the pivot table?

When the pp window displays its dataset with the extra calculated measure that depends on hasonevalue is it in the context of the entire pp window dataset (I think you call this the model)?

I still wonder why hasonevalue doesnt work in my pivot table.  But have to wonder how the plumbing works especially when my pp window shows blanks in the calc'd price column of the dataset and I'm expecting something different once the pivot table generates.

Maybe my other post will help here.  There i ask if display format in a pivot table can be controlled via expression so i can use same col to show currency in some situations and number in others.  The answer might offer a workaround here in which i'll never have to worry about price showing anywhere except the most detailed line.

One thing I'm thinking about already is just concatenating it as text to the lowest level label which is invoice #.

Thursday, January 30, 2014 7:22 PM
• Hi DB042189,

Is there any chance you could provide a version of the Power Pivot workbook with anonymised/test data? It may help to clarify some of the concepts that are being discussed in this thread if the community is able to apply them directly to a copy of the model. Ideally, you'd upload this anonymised Power Pivot workbook to a publicly accessible location such as SkyDrive or Dropbox folder.

I can understand if this isn't possible but just thought I'd ask.

Regards,

Michael

Monday, February 10, 2014 9:36 PM
• sure.  What's an easier path, skydive or dropbox?  Its been a long time since i used any kind of dropbox for this kind of thing.
Tuesday, February 11, 2014 3:16 PM
• In that case, SkyDrive would probably be best ;)
Tuesday, February 11, 2014 3:53 PM
• it looks like basic drop box is free and perhaps skydive isnt.  So i went with dropbox.  Try this link...

https://www.dropbox.com/s/g5mn7az39s5shw8/pocPowerPivot.xlsx

the first question is why drillprice isnt showing on the lowest level of row labels.  In the current state,  its fx is as shown below, I dont recall if I tried it with one value on price instead of PK.

The confusing part for me is what context is used when this fx is applied? Is it pp window (aka model?)?  Various labels on pivot table?  Does the fact that drillprice is always blank in the pp window have any effect on the pivot table?

=if(HASONEVALUE(sales[pk_sales]),[price],Blank())

• Edited by Tuesday, February 11, 2014 6:27 PM clear it up a little
Tuesday, February 11, 2014 5:10 PM
• Is this what you are trying to achieve?

If so then you can create the following calculated fields:

DrillPriceNew:=
IF(
ISFILTERED(sales[InvoiceNumber]),
CALCULATE(SUM(sales[price])),
Blank()
)

DrillExtendedPrice:=
IF(
ISFILTERED(sales[InvoiceNumber]),
CALCULATE(SUM(sales[ExtendedPrice])),
Blank()
)

Hope this helps,

Michael

EDIT: This solution assumes that InvoiceNumber is unique but if it's not and you have something like a line number then you can concatenate these together in a calculated column and then replace the column reference in the ISFILTERED() function with this new calculated column.

• Edited by Wednesday, February 12, 2014 9:24 AM small addition
• Marked as answer by Wednesday, February 12, 2014 1:36 PM
Wednesday, February 12, 2014 9:09 AM
• To help you to get a better understanding of evaluation contexts in DAX and the HASONEVALUE function, I recommend reading the following two official TechNet articles in the order listed:

Context in DAX Formulas

http://technet.microsoft.com/en-us/library/gg413423.aspx

HASONEVALUE Function (DAX)

http://technet.microsoft.com/en-us/library/gg492190.aspx

EDIT: The main issue you have been facing with the HASONEVALUE function is due to using it in a calculated column instead of a calculated field (measure)

The following two articles may help to clarify the difference between the two:

Calculated Fields in Power Pivot

http://office.microsoft.com/en-gb/excel-help/calculated-fields-in-power-pivot-HA102837191.aspx

When to Use Measures vs. Calc Columns

http://www.powerpivotpro.com/2013/02/when-to-use-measures-vs-calc-columns/

• Edited by Wednesday, February 12, 2014 10:07 AM additional info added
• Marked as answer by Wednesday, February 12, 2014 1:36 PM
Wednesday, February 12, 2014 9:34 AM
• thx.  Yes, except that extended price is ok being rolled up, ie no dax needed on extended price.   The drill thru simulation is achieved by showing price down on the most detailed level.  Your solution achieved that.   What this does is offer me an alternative to more expensive and less flexible SSRS solutions to the same class of problem.
Wednesday, February 12, 2014 1:35 PM
• OK, cool. Glad that it helped :)

As a best practice, it's recommended that you create the calculated measures explicitly as opposed to relying on the Power Pivot Field List to implicitly create them. There are a number of reason for this but one of the biggest ones is to help prevent your users from creating implicit measures with the wrong aggregations applied. It may also help to speed up the process of creating new ad-hoc reports.

Create a Measure in a PivotTable or PivotChart

http://technet.microsoft.com/en-us/library/gg399161.aspx

Naturally, this is a suggestion and you may have valid reasons for not following this practice, but wanted to highlight it just in case ;)

Regards,

Michael

Wednesday, February 12, 2014 2:15 PM
• thx.

I'll have to reread the last link.  Till I read it, I thought (explicitly) adding a calc'd field in the pp window would preclude needing to also add a new measure to the pt.  Seemed the first would would be available in the pt after refreshing fields and do everything I'd hope for.  Maybe I'll learn there is additional functionality in the pt that cant be achieved with the pp window's calc'd field alone.

Anyway, when time permits I'll leverage this sales example to follow thru on the other post about conditionally formatting cells in pp.  I'll add a fact table with employee counts and see if a numeric hybrid (counts and \$'s depending on row label) report can be achieved in pp.  I'm suspecting hasonevalue wont apply there but maybe I'm wrong now that the context picture is a bit clearer.

Wednesday, February 12, 2014 6:27 PM