Creating a table/visual with calculations based off what is in another table in Power BI

Question

I am working in Power BI and am not sure how to create another table or a visual that has calculations based off a table I already have. This is the data I have now and also what the end result would be. I also need to be able to filter the result by the date that is in the first table.

Answer 1

you can do the data transform in PQ

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtE31zcyUtJRikwthpMmQIwQ88sHEoZAbKwUq4PQZAKTAhOmYHkULeZgk2JjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Question 1a" = _t, #"Question 1b" = _t, #"Question 1c" = _t, #"Question 1d" = _t, #"Question 2a" = _t, #"Question 2b" = _t, #"Question 2c" = _t, #"Question 2d" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Question 1a", type text}, {"Question 1b", type text}, {"Question 1c", Int64.Type}, {"Question 1d", Int64.Type}, {"Question 2a", type text}, {"Question 2b", type text}, {"Question 2c", Int64.Type}, {"Question 2d", Int64.Type}}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
    #"Transposed Table" = Table.Transpose(#"Demoted Headers"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Date"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Date", "Question"}, {"Attribute", "Date"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each if Value.Type([Value])=type number then [Value] else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if [Custom] = null then [Value] else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Value"}),
    #"Inserted Last Characters" = Table.AddColumn(#"Removed Columns", "Question 2", each Text.End([Question], 1), type text),
    #"Inserted First Characters" = Table.AddColumn(#"Inserted Last Characters", "Question ", each Text.Start([Question], Text.Length([Question])-1), type text),
    #"Removed Columns1" = Table.RemoveColumns(#"Inserted First Characters",{"Question"})
in
    #"Removed Columns1"

then you can create measures

Total a = countx(FILTER('Table','Table'[Custom.1]="Yes"&&'Table'[Question 2]="a"),'Table'[Question])+0

Total b = countx(FILTER('Table','Table'[Custom.1]="Yes"&&'Table'[Question 2]="b"),'Table'[Question])+0

Total c = sumx(FILTER('Table','Table'[Question 2]="c"),'Table'[Custom])

Total d = sumx(FILTER('Table','Table'[Question 2]="d"),'Table'[Custom])