How do I SUM and calculate a percentage of specific cells in my matrix?

[Anonymous]

Hi,

I am new to PowerBI and I am trying to SUM the highlighted area in my matrix below. I want to also calculate a percentage based on that SUM divided by the total table (N = 900). Afterward, I would like to create a line chart that demonstrates the percentage over time. However, I am a bit stuck and unsure of how to do that. I have a column in my data source that indicates the time period too.

Here is a snapshot of my matrix and the highlighted cells I am trying to calculate:

This is the code for the specific highlighted cells:

[v-shex-msft]

HI @Anonymous,

I'd like to suggest you create a variable highlight table to store these highlighted fields, then you can use it as conditions to calculate in measure formula:

Measure =
VAR highlighted=
    DATATABLE (
        "Row", STRING,
        "Column", STRING,
        {
            { "5", "2" },
            { "5", "3" },
            { "5", "4" },
            { "5", "5" },
            { "4", "3" },
            { "4", "4" },
            { "4", "5" },
            { "3", "4" },
            { "3", "5" },
            { "2", "5" }
        }
    )
VAR filtered =
    CALCULATETABLE (
        Table,
        ALLSELECTED ( Table ),
        VALUES ( Table[RowField] ),
        VALUES ( Table[ColumnField] )
    )
RETURN
    DIVIDE (
        SUMX (
            FILTER (
                filtered,
                COUNTROWS (
                    INTERSECT ( ROW ( "Row", [RowField], "Column", [ColumnField] ), highlighted)
                ) > 0
            ),
            [ValueField]
        ),
        CALCULATE ( SUM ( Table[ValueField] ), ALLSELECTED ( Table ) ),
        -1
    )

Regards,

Xiaoxin Sheng

[amitchandak]

@Anonymous 

Create a new matrix to return the sum/count of the value whenever the color light green color supposes to return. Almost same measure return sum/count of measure for light green and blank() for others

[Anonymous]

@amitchandak how do i go about doing that? Whenever I try to use sum(count([Movement Color])), PowerBI errors out because the movement color function only returns string values of "lightgreen" or "". 

[amitchandak · Accepted solution]

I am not asking to the sum of count that. In all you ifs return a sum/count of measure instead of green. In place of other return blank()