How to use measure names as ROW values and the measure values as COLUMN in Power BI?

[Anonymous]

Hi All,

I am trying to reproduce the below tableau report in Power BI

I have the following measures in Power BI:

1. total_end (Correspond to Actual in table above), end_var, end_ytd, end_ytd_var

2. saidi (Correspond to Actual in table above), saidi_var, saidi_ytd, saidi_ytd_var

3. saifi (Correspond to Actual in table above), saifi_var, saifi_ytd, saifi_ytd_var

4. nbre (Correspond to Actual in table above), nbre_var, nbre_ytd, nbre_ytd_var

In my attempt to reporduce the table above in PBI, I have created a table called indicators with columns (name, actual, var, ytd, var_ytd). Column name has rows (END, SAIFI, SAIDI and NBRE) and using if-else statements, I have populated the other columns accordingly. With this approach I am able to display the data as shown above BUT now the challenge I have has to do with the date filters. When I select a month and year, the values in this created table doesn't change. Below is a sample code of how the table is populated:

actual = if (indicators[name] = "END",[total_end],if(indicators[name] = "SAIDI", [total_saidi], if(indicators[name] = "SAIFI", [total_saifi], if(indicators[name] = "NBRE", [nombre_ouvrage]))))

How can I make the values in this table to change when the date filters changes because now it is showing but the global values instead ? or is there a better approach or visual I can use?

My model is as follows:

[v-lili6-msft · Accepted solution]

hi, @Anonymous 

From your formula and screenshot, we could know that what you have created a new column in indicators table.

First, you must know that calculated column and calculate table can't be affected by any slicer. 
Notice:
1. Calculation column/table not support dynamic changed based on filter or slicer.
2. measure can be affected by filter/slicer, so you can use it to get dynamic summary result.
here is reference:
https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/
Second, you just need to create a measure instead of column by this formula:

Measure =
IF (
    SELECTEDVALUE ( indicators[name] ) = "END",
    [total_end],
    IF (
        SELECTEDVALUE ( indicators[name] ) = "SAIDI",
        [total_saidi],
        IF (
            SELECTEDVALUE ( indicators[name] ) = "SAIFI",
            [total_saifi],
            IF ( SELECTEDVALUE ( indicators[name] ) = "NBRE", [nombre_ouvrage] )
        )
    )
)

Then drag indicators[name] filed and this measure into a table visual, it will work well.

Regards,

Lin

[Anonymous]

Hi @v-lili6-msft ,

Thank you so much. It works perfectly

Best Regards,

Divo