Need help with SUM calculation based on filter

[Jit007]

Agent ID	Request#	Parameter Q1	Score
AA	RI001	Yes	15
AA	RI002	Yes	15
BB	RI003	No	0
BB	RI004	No	0

Total Checked	Total Yes	Total   No	Achieved %
4	2	2	50%

I trying to get the Achieved result using calculate(SUM + Filter calculation but its not working- not sure whether i am going correctly to get that result. Please direct me to correct solution. I am new to DAX & still learning. Thanks

Here I need to calculate Total Yes divide by Total Checked 

[dufoq3 · Accepted solution]

Hi @Jit007,

Result:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRU0lEK8jQwMATSkanFQNLQVClWB0nGCF3GyQkqYwyk/fKBhAGquAmSeCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Agent ID" = _t, #"Request#" = _t, #"Parameter Q1" = _t, Score = _t]),
    Ad_GroupHelper = Table.AddColumn(Source, "GoupHelper", each 1, Int64.Type),
    GroupedRows = Table.Group(Ad_GroupHelper, {"GoupHelper"}, {
        {"Total Checked", each Table.RowCount(_), Int64.Type},
        {"Total Yes", each Table.RowCount(Table.SelectRows(_, (x)=> x[Parameter Q1] = "Yes")), Int64.Type},
        {"Total No", each Table.RowCount(Table.SelectRows(_, (x)=> x[Parameter Q1] = "No")), Int64.Type} }),
    Ad_Achieved = Table.AddColumn(GroupedRows, "Achieved %", each [Total Yes] / [Total Checked], Percentage.Type),
    RemovedColumns = Table.RemoveColumns(Ad_Achieved,{"GoupHelper"})
in
    RemovedColumns

[gmsamborn · Accepted solution]

Hi @Jit007 

My solution is in DAX.

The measures are as follows:

Total Checked = 
    CALCULATE(
        COUNTROWS( 'Table' ),
        ALL( 'Table' )
    )

Total Yes = 
    CALCULATE(
        COUNTROWS( 'Table' ),
        'Table'[Parameter Q1] = "Yes"
    )

Total No = 
    CALCULATE(
        COUNTROWS( 'Table' ),
        'Table'[Parameter Q1] = "No"
    )

Achieved % = 
    DIVIDE(
        [Total Yes],
        [Total Checked]
    )

Let me know if you have any questions.

Jit007.pbix

[dufoq3 · Accepted solution]

Hi @Jit007,

Result:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRU0lEK8jQwMATSkanFQNLQVClWB0nGCF3GyQkqYwyk/fKBhAGquAmSeCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Agent ID" = _t, #"Request#" = _t, #"Parameter Q1" = _t, Score = _t]),
    Ad_GroupHelper = Table.AddColumn(Source, "GoupHelper", each 1, Int64.Type),
    GroupedRows = Table.Group(Ad_GroupHelper, {"GoupHelper"}, {
        {"Total Checked", each Table.RowCount(_), Int64.Type},
        {"Total Yes", each Table.RowCount(Table.SelectRows(_, (x)=> x[Parameter Q1] = "Yes")), Int64.Type},
        {"Total No", each Table.RowCount(Table.SelectRows(_, (x)=> x[Parameter Q1] = "No")), Int64.Type} }),
    Ad_Achieved = Table.AddColumn(GroupedRows, "Achieved %", each [Total Yes] / [Total Checked], Percentage.Type),
    RemovedColumns = Table.RemoveColumns(Ad_Achieved,{"GoupHelper"})
in
    RemovedColumns