PivotTable/Chart - how to add series to stacked column RRS feed

  • Question

  • Hi, I hope I'll be forgiven for asking what might seem a trivial question but I'm clueless and feel a bit like Bambi on ice - my attempts at creating something useful with PowerPivot despite not knowing anything about it came from wanting to give users more control over the data they view rather than the "reports" we impart on them.

    I'm going to try to describe the data as that from a sales system so imagine a number of rows where each such row can be said to belong to a category. For example, say we have three different categories, sports, finance, weather - the category is one of the columns in this data. Now imagine there are different targets and we're interested in seeing whether or not these have been met where columns have been added to indicate if the row should be considered for that particular target and also if it was met, just a boolean flag, and we call these indicators.

    Let's say there are two indicators for this example, Ind1 and Ind2. These indicators tell us whether or not the row in question should be considered when we're looking at our targets (two different targets). Assume furthermore there are two additional indicators that tells us whether or not the target was actually met - a 1 incidates the target was met. This is a pretty poor explanation so I hope an actual example with data will help.

    title | category | Ind1 | Ind2 | Ind1_ex | Ind2_ex

    a | sports | 1 | 1 | 0 | 1

    b | sports | 1 | 0 | 0 | 0

    c | finance | 0 | 1 | 0 | 1

    The way to read this is, first row (category is somewhat irrelevant really)

    Ind1 and Ind2 tells us this record is "valid" for both targets. Ind2_ex with a value of 1 tells us this record met the target defined by Ind2

    second row Ind1 with a value of 1 tells us this row is "valid" for the target defined by Ind1. However, Ind1_ex is 0 and so the target was not met.

    I hope that makes it easier to understand the above text.

    Assume there are 100 records: 20 of those having a Ind1 = 1 (and of those, 5 have a value of 1 in Ind1_ex) 70 of those have a Ind2 = 1 (and of those, 60 have a value of 1 in Ind2_ex)

    What I'm trying to do is basically a pivotchart where, in this case, I would like two bars (bar chart), as I consider there to be two series (Ind1 and Ind2). I'm trying to get some kind of stacked column where the first one (for Ind1) would show 75% with the rest, 25% made up of the count for Ind1_ex (20 records qualify for the target. Out of those 20 records, 15 didn't meet the target, 75% with 25% having met the target) Similarly, I'd expect the second column, series, to show roughly 86% with the rest being the % of Ind2_ex.

    What I did was create a PivotTable with this data. I started out by adding Ind1 and Ind1_ex to the "Values" pane of the PowerPivot Field List. I can then insert a column with a 100% stacked column and this shows Ind1 in a graph as described above. However, what I was hoping to do next was add Ind2 in that same graph. If I just go ahead and add Ind2 and Ind2_ex to the "Values" pane of the PowerPivot Field List, all of these then end up forming a single column where the total makes up the 100%.

    Once again, what I was hoping to see was two separate columns, one for Ind1 and the other for Ind2. I apologise for the poor explanation but I hope someone might be able to make sense of it and get me on the right track. And yes, it should be clear that I'm clueless and in dire need of some help, of which any would be much appreciated. Cheers /Sakic

    • Edited by Sakic21 Thursday, March 29, 2012 3:30 AM formatting
    Thursday, March 29, 2012 3:27 AM


All replies

  • Hi,

    It is a little diffcult to understand your requirement, could you please be more specific about your issue?

    Challen Fu

    TechNet Community Support

    Thursday, April 5, 2012 2:50 AM
  • Hi Challen,

    Thanks for your reply, much appreciated.

    I apologise for the poor description. Perhaps best ignore and I'll have another go at it. Not sure I'll do a better job second time around but here goes...

    I'll use the same data structure as before

    title | category | Ind1 | Ind2 | Ind1_ex | Ind2_ex
    a | sports | 1 | 1 | 0 | 1
    b | sports | 1 | 0 | 0 | 0
    c | finance | 0 | 1 | 0 | 1

    Columns named Ind are basically just flags with boolean values (1 and 0, true and false). The basic concept is that an Ind column indicates whether or not the row should be considered for an indicator. The Ind_ex column indicates if the row in question is considered to have met the criteria for the indicator.

    Using an ice hockey analogy, one might say that Ind1 indicates if Lemieux played in the game signified by the row in question and Ind1_ex whether or not he scored. Ind2 indicates if Gretzky played in the game and Ind2_ex if Gretzky scored or not.

    What I'm ultimately trying to achieve is a graph with one series (column) per indicator.

    Using the above hockey analogy, I'd be interested in seeing either numbers or a % the number of games in which the players scored; in this case two series, one for Lemieux and one for Gretzky.

    Lemieux might have played in 10 games (ten rows with Ind1 = 1) and scored in 7 (ten rows where Ind1 = 1 and Ind1_ex = 1). Gretzky might have played 8 games and scored in 4.

    What I'd be interested in is a graph that shows the two series; the one for Lemieux would show a bar with 70% (as he scored in 70% of the games) with the rest (30%) made up of the ones he didn't score. I suppose this is why I thought the concept of the stacked column seemed to make sense. For Gretzky the graph would show 50% (scored in 50% of his games) with the rest (50%) made up of the games he didn't score.

    I don't know if this explanation is any better but if not, please let me know and I'll try with another example. Any help would be much appreciated.

    Wednesday, April 11, 2012 2:19 AM
  • hi kindly refer the below link, may be it is helpful for you.


    Wednesday, May 2, 2012 10:51 AM
  • Sakic,

    Is this still an issue?

    Thank you!

    Ed Price, SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Monday, October 28, 2013 7:07 AM