PowerPivot and Excel: Using a dynamic label in a chart

Posted on Author Kasper 12

I got this interesting question on my ask a questions page that I would like to answer by writing a blog post.

The question was the following:

I have a table fetched from a SQL database that contains a few fields but the interested ones are ComputerID and SystemStartupTime which is the time a computer takes to boot.
I have been asked to display this information on a graph averaged by the average of each ComputerID which I did like this:
myWonderfulMeasure:=ROUND(AVERAGEX(SUMMARIZE(myTable,myTable[ComputerID],”AveragePerComputer”,AVERAGE([SystemStartupTime])),[AveragePerComputer]), 0)
The graph using this measure shows those values by Year and WeekNumber successfully using slicers to play with different variables.

Now I have to display this average on another graph by segments of 30 seconds. This means, by number of machines that booted in:
– 0 to 30 seconds
– 31 to 60 seconds
– …

I wanted to use an autocalculated column. However I lose the average per computer (as the table lists all of the boot times).
I then tried to create a new measure with a long and dirty IF:
=if(Table[myWonderfulMeasure]<=30, “0-30”, if(Table[myWonderfulMeasure]<=60, “30-60”, [..] ))
However it doesn’t work at all. I can only use this measure as value (which displays nothing on the graph except 0, 0.1, .., 0.9, 1 on the y axe, and no bar) I can’t put the measure on the Legend Field for instance.

Unfortunately it is not possible to use a measure and put it on a label in Excel (but it is possible in Power View for non text meaures). So we need another solution. We can solve this in the exact opposite way. Lets look at an example using adventureworks with the FactInternetSales and DimProduct (filtered to only contain the current items) table:

I want to see the number of products per color per salesgroup where salesgroup is defined in Large, Medium and Small. The first idea that comes to mind is to create a measure that does what we want. That is pretty straightforward but unfortunately doesn’t work in Excel. So we need to come up with another solution.

What I did is define a table in Excel that defines the SalesGroup with Labels and min and max values:

Now I load this into PowerPivot as a linked table (no relationships created) and start by creating a pivottable using Color and the Label

Now I can create a measure that determines the number of products that fall between the min and max for each SalesGroup:

=if(HASONEVALUE(Categorygroup[Label]), 
		COUNTROWS( 
			FILTER(DimProduct, 
				[Sum of SalesAmount] >= VALUES(Categorygroup[Low]) 
				&& [Sum of SalesAmount] < VALUES(Categorygroup[High])
				)
			)
	, COUNTROWS(DimProduct))

This gives us the result we wanted:

or create a chart from it:

Lets look at this measure step by step:

  1. if(HASONEVALUE(categorygroup [Label]), … , COUNTROWS(DimProduct)): Do we have a single categorygroup selected in the pivottable ? If yes we can select a range, if more are selected return COUNTROWS(DimProduct)
  2. COUNTROWS(: Count the number of rows
  3. FILTER(DimProduct,  : From a filtered DimProducttable (observe that the filter created by putting colors on rows in effect as well)
  4. [Sum of SalesAmount] >= values(categorygroup [Low])
    && [Sum of SalesAmount] < VALUES(categorygroup [High])) : Filter all rows in the DimProduct table where a measure [Sum of SalesAmount] is greater or equal then the value of categorygroup [Low] for the current categorygroup (using VALUES) and [Sum of SalesAmount] smaller then the value of categorygroup [High]

Hope this showed a simple approach to create a solution for Excel that gives you dynamic labels.

12 Replies to “PowerPivot and Excel: Using a dynamic label in a chart”

  1. Hello Kasper and thank you very much for your help in regard to this problem.
    I have tried your solution and think I’m pretty close to have it working.
    There is still an issue though.
    As “”Sum of SalesAmount” I have used a measure I created (mentioned in the message I posted as question) instead of the field of a table. As soon as I do so, I can display the values as PivotTable but not in a chart. I get the following error message, would you happen to have any idea ? Thank you again!

    ============================
    Error Message:
    ============================

    Exception from HRESULT: 0x800A03EC
    —————————-

    —————————-
    Could not add the field “30s Segments” to the PivotTable because the formula is invalid.

    ============================
    Call Stack:
    ============================

    Server stack trace:

    Exception rethrown at [0]:
    at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
    at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
    at Microsoft.Office.Interop.Excel.PivotTable.AddDataField(Object Field, Object Caption, Object Function)
    at Microsoft.AnalysisServices.XLHost.Addin.ExcelInterOpUtil.AddToDataFields(ICalculatedMember calculatedMember, Int32 positionIndex, Boolean isSpecialColumnBasedNamedSetPresent)
    —————————-
    at Microsoft.AnalysisServices.XLHost.Addin.ExcelInterOpUtil.AddToDataFields(ICalculatedMember calculatedMember, Int32 positionIndex, Boolean isSpecialColumnBasedNamedSetPresent)
    at Microsoft.AnalysisServices.XLHost.Addin.GeminiPivot.SetFieldOrientation(PivotArea location, IGeminiColumn column, Int32 positionIndex)
    —————————-
    at Microsoft.AnalysisServices.XLHost.Addin.GeminiPivot.SetFieldOrientation(PivotArea location, IGeminiColumn column, Int32 positionIndex)
    at Microsoft.AnalysisServices.XLHost.Addin.GeminiPivot.AddToFieldListInternal(PivotArea location, IGeminiColumn column, Int32 positionIndex)
    at Microsoft.AnalysisServices.XLHost.Addin.GeminiPivot.MoveField(IGeminiColumn column, PivotArea source, PivotArea target, Int32 positionIndex)
    at Microsoft.AnalysisServices.XLHost.Addin.FieldListControl.MoveFieldInternal(IGeminiColumn field, PivotArea sourceArea, PivotArea targetArea, Int32 targetIndex)

    ============================

    Reply

  3. When I was writing my lengthy message I noticed the problem must be coming from memory.
    Because I can actuall create the PivotChart but as soon as I add a slicer it says there’s not enough memory or that I should switch to 64-bit.
    I will do so and see if it works better.

    Thank you !

    Reply

    1. Hi Dynek,

      How did you find out it was a memory problem. There should never be a error like that if something is wrong. We should give the user a message not throw errors. Can you still file a bug on the connect site ?

      Thanks,
      Kasper

      Reply

  4. How nice ! Thank you so much. I got it working using 64-bit version of Excel and have the graph showing up exactly as expected. I greatly appreciate your help!

    Reply

  5. @Kasper de Jonge
    Well instead of adding Label first, then measure I did the opposite and I got the right error message about memory (architecture).
    As I switched to 64-bit now I wanted to reproduce the issue in a 32-bit virtual environment with even less memory and it’s working perfectly. I have no idea what happened.

    Reply

  6. I came across another really weird issue.
    Using this method I get different result (different graphics!) when I use 32-bit or 64-bit version of Win7, Office and PowerPivot.

    Reply

  7. Hi Kasper,

    Thanks for your blog posts, they are of great help!
    I seem to have some problems with the summarize function. I have a few measures based on the summarize function (I think about 8, but actually often the same ‘virtual table’), My main data has about 700 000 rows, and the excel file is only about 15 Mb. I would think that this would work, as it is not that big.

    Still I get a memory error that starts to drive me crazy:
    “Data could not be retrieved from the external data source. Error message returned by the external data source:
    The operation has been cancelled because there is not enough memory available for the application. If using a 32-bit version of the product, consider upgrading to the 64-bit version or increasing the amount of memory available on the machine.”

    I’m not sure that I will be able to get a 64-bit version of excel. Can you think of any other ways to make my code more efficient? I’ve tried everything from reducing the number of columns to reducing the number of decimal digits, but it looks as if the main problems are the measures using ‘summarize’?

    Reply

  10. @Kasper de Jonge
    Hi Kasper,
    Thanks for your reaction!

    This is an example of one of the functions where I use the summarize function:
    MaxX(SUMMARIZE(filter(TimeSeries,TimeSeries[PowerReal]>0), TimeSeries[Counter], “Power”,TimeSeries[PowerReal]),([Power]>0)*max([Power]))

    * TimeSeries is a table with a counter (for the date) and with power production data of different locations (each location has one value for each time step (counter)). There are positive and negative values (positive when production, negative when consumption)
    * TimeSeries[PowerReal] is a measure which calculates the sum of the power production data of different locations (this is a measure since it also multiplies each time with location-specific data)
    * TimeSeries[Counter] is the associated time stamp (one value for each quarter) – linked to a Dates table

    I want to find the maximum power production over the year in the whole portfolio. To do this, I first need to sum up all production data for each time step separately, as I have positive and negative values and I only want to check the positive ones. That’s why I first make this virtual table with the ‘summarize’ function.

    On several blogs I read that the maximum file size for a 32-bit version is about 300Mb before you hit the memory limit. With mine I already hit this limit with my file which today is 12Mb.

    Thanks,
    Jan

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.