Forum Discussion

SirJJAnderson's avatar
SirJJAnderson
Helper III
6 years ago
Solved

How to create a visual like this?

How would I go about creating a visual like this excel?

 

 

I tried creating the below visual with dates for columns and my metrics for rows, but the metric data doesn't get put under the column. Ideally, the metric label should be the row, and the metric data should live in the columns.

 

Need Help
  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    6 years ago

    Hi SirJJAnderson ,

     

    We can use the following steps to meet your requirement.

     

    1. We need to create a new table that contains results table framework.

     

    DimTable = 
CROSSJOIN (
    DATATABLE (
        "RowName", STRING,
        "RowSort", INTEGER,
        {
            { "Deliveries", 1 },
            { "Open Rate", 2 },
            { "Opens", 3 },
            { "Visit Rate", 4 },
            { "Visits", 5 }
        }
    ),
    DATATABLE (
        "ColumnName", STRING,
        "ColumnSort", INTEGER,
        {
            { "Last 30 Days", 1 },
            { "30 Days Prior", 2 },
            { "% Change", 3 }
        }
    )
)

     

     

    2. Then we can unpivot the RowName in Power Query Editor.

     

     

     

    3. And we can create a measure in Dim table to calculate the value and format it.

     

    Measure = 
FORMAT (
    SWITCH (
        SELECTEDVALUE ( 'DimTable'[ColumnName], BLANK () ),
        "Last 30 Days", CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER (
                'Table',
                'Table'[Row] IN DISTINCT ( 'DimTable'[RowName] )
                    && 'Table'[Send Date]
                        >= TODAY () - 30
                    && 'Table'[Send Date] <= TODAY ()
            )
        ),
        "30 Days Prior", CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER (
                'Table',
                'Table'[Row] IN DISTINCT ( 'DimTable'[RowName] )
                    && 'Table'[Send Date]
                        < TODAY () - 30
            )
        ),
        "% Change", ABS (
            DIVIDE (
                CALCULATE (
                    SUM ( 'Table'[Value] ),
                    FILTER (
                        'Table',
                        'Table'[Row] IN DISTINCT ( 'DimTable'[RowName] )
                            && 'Table'[Send Date]
                                >= TODAY () - 30
                            && 'Table'[Send Date] <= TODAY ()
                    )
                ),
                CALCULATE (
                    SUM ( 'Table'[Value] ),
                    FILTER (
                        'Table',
                        'Table'[Row] IN DISTINCT ( 'DimTable'[RowName] )
                            && 'Table'[Send Date]
                                < TODAY () - 30
                    )
                ),
                0
            ) - 1
        )
    ),
    SWITCH (
        TRUE (),
        CONTAINSSTRING ( SELECTEDVALUE ( DimTable[RowName], BLANK () ), "Rate" )
            || CONTAINSSTRING ( SELECTEDVALUE ( DimTable[ColumnName], BLANK () ), "%" ), "Percent",
        "General Number"
    )
)

     

     

    4. At last, we need to create a color measure to configure the % change’s color.

     

    Color = 
IF (
    SELECTEDVALUE ( DimTable[ColumnName], BLANK () ) = "% Change",
    IF (
        DIVIDE (
            CALCULATE (
                SUM ( 'Table'[Value] ),
                FILTER (
                    'Table',
                    'Table'[Row] IN DISTINCT ( 'DimTable'[RowName] )
                        && 'Table'[Send Date]
                            >= TODAY () - 30
                        && 'Table'[Send Date] <= TODAY ()
                )
            ),
            CALCULATE (
                SUM ( 'Table'[Value] ),
                FILTER (
                    'Table',
                    'Table'[Row] IN DISTINCT ( 'DimTable'[RowName] )
                        && 'Table'[Send Date]
                            < TODAY () - 30
                )
            ),
            0
        ) - 1 >= 0,
        "#FF0000",
        "#00FF00"
    ),
    BLANK ()
)

     

     

    And the result like this,

     

     

    If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

    BTW, pbix as attached.       

     

    Best regards,

     

    Community Support Team _ zhenbw

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

8 Replies

  • nandukrishnavs's avatar
    nandukrishnavs
    Community Champion
    6 years ago

    SirJJAnderson 

     

    You can use Table/Matrix. You have to create 3 measures Last 30 Days, 30 Days Prior, and % Changes. Then apply conditional formatting for % Changes measure. 



    Did I answer your question? Mark my post as a solution!
    Appreciate with a kudos     🙂

    • SirJJAnderson's avatar
      SirJJAnderson
      Helper III
      6 years ago

      I hate to ask this, but I'm a Power BI noob... how would I go about creating those measures? Is there a formula I can follow for each? Do I need to adjust or create a new date table?

  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    Community Support
    6 years ago

    Hi SirJJAnderson ,

     

    We can use the following steps to meet your requirement.

     

    1. We need to create a new table that contains results table framework.

     

    DimTable = 
CROSSJOIN (
    DATATABLE (
        "RowName", STRING,
        "RowSort", INTEGER,
        {
            { "Deliveries", 1 },
            { "Open Rate", 2 },
            { "Opens", 3 },
            { "Visit Rate", 4 },
            { "Visits", 5 }
        }
    ),
    DATATABLE (
        "ColumnName", STRING,
        "ColumnSort", INTEGER,
        {
            { "Last 30 Days", 1 },
            { "30 Days Prior", 2 },
            { "% Change", 3 }
        }
    )
)

     

     

    2. Then we can unpivot the RowName in Power Query Editor.

     

     

     

    3. And we can create a measure in Dim table to calculate the value and format it.

     

    Measure = 
FORMAT (
    SWITCH (
        SELECTEDVALUE ( 'DimTable'[ColumnName], BLANK () ),
        "Last 30 Days", CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER (
                'Table',
                'Table'[Row] IN DISTINCT ( 'DimTable'[RowName] )
                    && 'Table'[Send Date]
                        >= TODAY () - 30
                    && 'Table'[Send Date] <= TODAY ()
            )
        ),
        "30 Days Prior", CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER (
                'Table',
                'Table'[Row] IN DISTINCT ( 'DimTable'[RowName] )
                    && 'Table'[Send Date]
                        < TODAY () - 30
            )
        ),
        "% Change", ABS (
            DIVIDE (
                CALCULATE (
                    SUM ( 'Table'[Value] ),
                    FILTER (
                        'Table',
                        'Table'[Row] IN DISTINCT ( 'DimTable'[RowName] )
                            && 'Table'[Send Date]
                                >= TODAY () - 30
                            && 'Table'[Send Date] <= TODAY ()
                    )
                ),
                CALCULATE (
                    SUM ( 'Table'[Value] ),
                    FILTER (
                        'Table',
                        'Table'[Row] IN DISTINCT ( 'DimTable'[RowName] )
                            && 'Table'[Send Date]
                                < TODAY () - 30
                    )
                ),
                0
            ) - 1
        )
    ),
    SWITCH (
        TRUE (),
        CONTAINSSTRING ( SELECTEDVALUE ( DimTable[RowName], BLANK () ), "Rate" )
            || CONTAINSSTRING ( SELECTEDVALUE ( DimTable[ColumnName], BLANK () ), "%" ), "Percent",
        "General Number"
    )
)

     

     

    4. At last, we need to create a color measure to configure the % change’s color.

     

    Color = 
IF (
    SELECTEDVALUE ( DimTable[ColumnName], BLANK () ) = "% Change",
    IF (
        DIVIDE (
            CALCULATE (
                SUM ( 'Table'[Value] ),
                FILTER (
                    'Table',
                    'Table'[Row] IN DISTINCT ( 'DimTable'[RowName] )
                        && 'Table'[Send Date]
                            >= TODAY () - 30
                        && 'Table'[Send Date] <= TODAY ()
                )
            ),
            CALCULATE (
                SUM ( 'Table'[Value] ),
                FILTER (
                    'Table',
                    'Table'[Row] IN DISTINCT ( 'DimTable'[RowName] )
                        && 'Table'[Send Date]
                            < TODAY () - 30
                )
            ),
            0
        ) - 1 >= 0,
        "#FF0000",
        "#00FF00"
    ),
    BLANK ()
)

     

     

    And the result like this,

     

     

    If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

    BTW, pbix as attached.       

     

    Best regards,

     

    Community Support Team _ zhenbw

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.