Place measure in matrix row section and conditionally format it

[user_95]

Hello all,

I am trying to build a matrix visual similar to below. However, I am facing issue with placing the Availability% KPI in the desired location as per the requirement and conditionally formatting it.

I have created the desired matrix visual and calculated this KPI as a measure separately, but unable to place it in the same matrix, as measures can't be added under matrix rows. I tried to create it as a column as well but the DAX is not returning the right results and even if I do resolve it, it would need to be placed under rows (but matrix rows don't have conditional formatting). It would be helpful if there is any way I can place it atleast at the end in the Values section as well (like a total), but the date field in column must not impact it.

Below is the required design and what I have built in PBI currently.

Availability% = DIVIDE(DISTINCTCOUNT(Date),7)

Would appreciate if you have any idea to get it in the desired format. Thanks! 

Required Design

Current Report

[v-stephen-msft]

Hi @user_95 ,

Pls has your problem been solved? If so, accept the helpful reply as a solution or share us with your own insights. This will make it easier for the future people to find the answer quickly.
If not, please provide a more detailed description about it.

Best Regards,
Stephen Tao

[Jihwan_Kim · Accepted solution]

Hi,

I am not 100% sure if I understood your question correctly, but I tried to create a sample pbix file like below.

One of ways is to create a new table (column table) for the purpose of making the column values in the matrix visual like below.

available %: = 
VAR _alldates =
    COUNTROWS ( 'calendar' )
VAR _available =
    COUNTROWS ( FILTER ( data, data[available] = 1 ) )
VAR _t =
    ADDCOLUMNS (
        VALUES ( 'calendar'[date] ),
        "@availability%",
            CALCULATE (
                DIVIDE (
                    COUNTROWS ( FILTER ( data, data[available] = 1 ) ),
                    COUNTROWS ( 'calendar' )
                )
            )
    )
RETURN
    IF (
        HASONEVALUE ( location[location] ),
        SWITCH (
            TRUE (),
            SELECTEDVALUE ( column_table[column] ) = "Availability %",
                DIVIDE (
                    COUNTROWS ( FILTER ( data, data[available] = 1 ) ),
                    COUNTROWS ( 'calendar' )
                ),
            MAXX (
                FILTER ( _t, 'calendar'[date] = MAX ( column_table[column] ) ),
                [@availability%]
            )
        )
    )