Show all values in chart and not a sum

[Rapaille]

Hello,

I want to create a column chart with test data. Sometimes there are more tests on same date and I want to show them all. I cannot find this option.

Who can help me?

Thank you in advance for your help.

Kind regards,

Hans

[v-yalanwu-msft]

Hi, @Rapaille ;

I came up with a method that adds a grouped index and turns them into 10^index; replace with n blank bits.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUbI0ABH6hib6RgZGRkqxOhBhM+zC5qYQYVNUYQs04VgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Score = _t, #"Publish Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Score", Int64.Type}, {"Publish Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Publish Date"}, {{"all", each Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type), type table }}),
    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"ID", "Score", "Index"}, {"ID", "Score", "Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded all", "Custom", each Number.Power(10,[Index])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Custom", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1","1","a",Replacer.ReplaceText,{"Custom"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","0","b",Replacer.ReplaceText,{"Custom"}),
    #"Added Custom1" = Table.AddColumn(#"Replaced Value1", "Custom.1", each [Custom]&Text.From([Publish Date])),
    #"Replaced Value2" = Table.ReplaceValue(#"Added Custom1","a"," ",Replacer.ReplaceText,{"Custom.1"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","b"," ",Replacer.ReplaceText,{"Custom.1"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Replaced Value3",{"Custom"})
in
    #"Removed Columns1"

the final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yalanwu-msft · Accepted solution]

Hi, @Rapaille ;

You could add index column in power query .

Then add index column in clustered column chart's x-axis.

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Rapaille]

Thank you very much for your help. This is my solution. 

One additional question: the index number is now visible in the graph. Is it possible to hide the index # and still have the date visible?

[Rapaille]

Thank you for your answer. When I do so, the chart does not recognize and show any values.

[thomthom_uk]

Hello,

EDIT:

I overlooked one simple solution. There is a don't summarize option in the column tools too:

Make sure the related visual doesn't sum up the values too. If that doesn't work, use the below method.

Click on the column, and under column tools, change the data type to 'text'.

If your value column is linked to sum measures or calculations, create a new column in the table, with the following formula:

text_column_name = CONVERT([table.value_column_name],STRING)

[Rapaille]

Thank you for your answer. When I do so, the chart does not recognize and show any values.