Create Data - PowerBI (Newbie)

[psycoperl]

I am new to PowerBI so I appreciate any help you can provide me.

I have two tables that I have added to my PowerBI Desktop file (LevelList and SCFE_ResponseRate)

in SCFE_ResponseRate I have data on a granular level By Year, STRM, Division, Level. The data being Number of Classes, Surveys Sent, Responses, ResponseRate

I would like to display a high level by Year/STRM level Now many Surveys were sent and how man responses were received. Then have the Response Rate calculated (Responses/Surveys Sent)

How can I do this. When I try to use the existing ResponseRate column, it just adds up the existing percentages in the field and does not calculate it.

 

[SullyWYO]

Not sure if I am understanding this correctly, but I suspect that you need to use the "Group By" feature, then create a custom column to calculate the response rate:

let
    Source = Excel.CurrentWorkbook(){[Name="SCFE_ResponseRate"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"STRM", type text}, {"Surveys Sent", Int64.Type}, {"Responses", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Year", "STRM"}, {{"Surveys Sent", each List.Sum([Surveys Sent]), type nullable number}, {"Responses", each List.Sum([Responses]), type nullable number}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Response Rate", each [Responses]/[Surveys Sent]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Response Rate", Percentage.Type}})
in
    #"Changed Type1"

 

[psycoperl]

Not sure if I am understanding this correctly, but I suspect that you need to use the "Group By" feature, then create a custom column to calculate the response rate:

let
    Source = Excel.CurrentWorkbook(){[Name="SCFE_ResponseRate"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"STRM", type text}, {"Surveys Sent", Int64.Type}, {"Responses", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Year", "STRM"}, {{"Surveys Sent", each List.Sum([Surveys Sent]), type nullable number}, {"Responses", each List.Sum([Responses]), type nullable number}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Response Rate", each [Responses]/[Surveys Sent]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Response Rate", Percentage.Type}})
in
    #"Changed Type1"

How do you add the Group By from the UI?

 

[SullyWYO]

I use Excel, so I'm not sure if it might be different in Power BI.

Here is a good tutorial for Excel: Grouping / Summarizing Rows
Here is something I found for Power BI: Group By Feature - Power BI

Hope that helps