graph - Need help graphing a time series by category and grand total of all categories in Power BI - Stack Overflow

admin管理员组

文章数量:1122846

I have a data frame with columns year, category, and value. I would like to plot this data in Power BI as follows: For each category separately, plot the sum of "value" by year. In addition, plot the yearly grand totals of the values (I can do all of this except for the grand totals graph). Hopefully the pictures of the data frame and plot below will make the question clear. Thanks!

I have a data frame with columns year, category, and value. I would like to plot this data in Power BI as follows: For each category separately, plot the sum of "value" by year. In addition, plot the yearly grand totals of the values (I can do all of this except for the grand totals graph). Hopefully the pictures of the data frame and plot below will make the question clear. Thanks!

• graph
• powerbi
• summary

Share Improve this question Follow asked Nov 22, 2024 at 1:30 Chris JChris J 3344 bronze badges

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 0

I did all this in Powerquery. "Group by Year and Category" is one query (Year, Category, Total Value)

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlLSUXIEYkOlWB0UAWNkAScgNoUJGENVGCELgFSYoQtYwARMoALGyAKOyIaaIGyJBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, Category = _t, value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Category", type text}, {"value", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Year", "Category"}, {{"Total Value", each List.Sum([value]), type nullable number}})

in #"Grouped Rows"

second query is "All Query" (Year, "All", Total Value)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlLSUXIEYkOlWB0UAWNkAScgNoUJGENVGCELgFSYoQtYwARMoALGyAKOyIaaIGyJBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, Category = _t, value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"value", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","A","All",Replacer.ReplaceValue,{"Category"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","B","All",Replacer.ReplaceText,{"Category"}),
    #"Grouped Rows" = Table.Group(#"Replaced Value1", {"Year", "Category"}, {{"Total Value", each List.Sum([value]), type nullable number}})
in
    #"Grouped Rows"

Final query unions those two together:

AppendGroupByAndAll

let
    Source = Table.Combine({All_Query, Group_By_Year_And_Category})
in
    Source

then you just graph the last one as you were doing in your example.

you can create a new table and sort the category column by sort column

do not create relationship between two tables

then create a measure

MEASURE =
SWITCH (
    TRUE (),
    SELECTEDVALUE ( 'Table (2)'[category] ) = "A", CALCULATE ( SUM ( 'Table'[value] ), 'Table'[category] = "A" ),
    SELECTEDVALUE ( 'Table (2)'[category] ) = "B", CALCULATE ( SUM ( 'Table'[value] ), 'Table'[category] = "B" ),
    CALCULATE ( SUM ( 'Table'[value] ) )
)

本文标签：