Forum Discussion

AllanXu's avatar
AllanXu
Icon for Helper II rankHelper II
10 years ago
Solved

Cannot have individual values in a chart. Values are always aggregate.

  Hello,   I am going through this tutorial https://support.powerbi.com/knowledgebase/articles/471597 The problem is that the UnitsInStock is always an aggregate. I cannot have individual values ...
Need Help
  • RJ's avatar
    RJ
    10 years ago

    Hi Allan

     

    I have not got time to look at your file. But what I suggest is you do this

     

    Go into ---->  Home  / Edit Queries / Transform / Data Types

     

    And see what Data Type your relevant columns have been set to

     

    What I think is one is set as Text (UnitsInStock) and the other one as a value

     

    You can see the difference below. One lineNumber I have set as text (Line Number) and one as a number (Line NumCopy). This is clearly shown by the symbol verse missing symbol between the tick box and the file name)

     

    THE ONLY THING POWER BI CAN DO WITH TEXT IS A COUNT or countdistinct if its dragged into a value box. Thats why it shows as count'

     

    Whereas a column set as a number format can be summed Averaged etc as shown below. (For some reason this does not show in the box as SumLineNumCopy. I assume the reason for this is to clealry show Text entries as opposed to non text entries)

     

    Grapph.GIF

     

    So what you might need to do is change the UnitsInStock to a number format

     

    If this isn't the issue someone else will have to help you out

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
10 years ago

One other thought here, why wouldn't the sum aggregation work for this? I haven't looked at the data itself, but in theory, if you only have a single value for units in stock per product, then the sum aggregation is summing 1 value for each product, which would be the value you want. x*1=x?

 

If you had mulitple entries for that product, then wouldn't you want the sum as well so that you know about all of your units in stock? The filters you put in place would allow you to only get a single location, etc.??

  • AllanXu's avatar
    AllanXu
    Icon for Helper II rankHelper II
    10 years ago

    Here is more information. I just need to underestand what is wrong with the field "UnitsInStock"

     

    Problem 1.png

     

    Problem 2.png

    • RJ's avatar
      RJ
      Icon for Resolver II rankResolver II
      10 years ago

      1 Are you saying if you drag unitinstock to the box under Value ("drag data field here" box) it appears in Visual level filter instead?

       

      2 The linevalue is in the value box. Surely it would be an aggrgation

      • AllanXu's avatar
        AllanXu
        Icon for Helper II rankHelper II
        10 years ago

        Hi RJ,

         

        The lazer focus of this post is that why UnitsInStock behaves different than other numeric columns such as LineTotal. Is this a bug in PBI or a feature that I don't know?

         

        >>1 Are you saying if you drag unitinstock to the box under Value ("drag data field here" box) it appears in Visual level filter instead?

        No. I am saying when I drag unitinstock into the bar chart, it appears in Visual level filter instead. This behavior is different than LineTotal. LineTotal appears in Values

         

         

        2 The linevalue is in the value box. Surely it would be an aggregation

        That is not true. What it is not the same as LineTotal?