How to create a visual like this?

[SirJJAnderson]

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.

[v-zhenbw-msft · Accepted solution]

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.

[nandukrishnavs]

@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]

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?

[nandukrishnavs]

@SirJJAnderson  Please share sample table (not image) and expected output.

[SirJJAnderson]

@nandukrishnavs not sure if the below works? Expected output would be a sum of deliveries, opens and visits for Last 30 Days and the 30 Days Prior, and the percentage change.

Send Date	Platform	Business Segment Type	Category	Email Name	Subject	Deliveries	Open Rate	Opens	Visit Rate	Visits
3/10/20	HubSpot	Agent/Advisor	Prospecting	A53	How are you adapting to new best interest regulations?	18,484	11.70%	2,163	3.19%	69
3/10/20	HubSpot	GA/BGA	Prospecting	G4	Considered Automating Your Policy Review Process?	399	16.29%	65	0.00%	0
3/17/20	HubSpot	Agent/Advisor	Prospecting	A54	Navigating the nuances of best interest standards	19,032	10.55%	2,007	1.84%	37
3/17/20	HubSpot	GA/BGA	Prospecting	G5	Data-Driven Decision Making	695	19.14%	133	1.50%	2
3/19/20	HubSpot	Multiple	Newsletter	Newsletter - March	A Message from our Founder – Mike Pepe	19,910	14.69%	2,925	2.05%	60
3/24/20	HubSpot	GA/BGA	Prospecting	G6	How are you safeguarding clients’ best interests?	791	18.96%	150	1.33%	2
3/24/20	HubSpot	Agent/Advisor	Prospecting	A55	How do you ensure your client recommendations are suitable?	19,858	12.82%	2,546	1.92%	49
3/29/20	HubSpot	Multiple	Webinar	WE1 - Clarity in Uncertain Times	You're Invited!	19,843	23.83%	4,728	1.52%	72
4/1/20	HubSpot	Multiple	Webinar	WE2 - Clarity in Uncertain Times	Reminder - Have you registered for “Helping Provide Clarity in Uncertain Times”?	16,085	16.08%	2,586	1.31%	34
4/2/20	HubSpot	Multiple	Webinar	WE3 - Clarity in Uncertain Times	1 Hour till Our Webinar Begins - Register Now!	15,970	13.20%	2,108	2.66%	56
4/7/20	HubSpot	Multiple	Webinar	WE1 - Simplifying Complexities	How are your clients’ policies being affected by COVID-19?	16,099	13.21%	2,126	1.51%	32
4/14/20	HubSpot	Multiple	Webinar	WE2 - Simplifying Complexities	Your clients need you	15,805	13.62%	2,152	1.16%	25
4/16/20	HubSpot	Multiple	Newsletter	Newsletter - April	A Letter from Our CEO – Kris Beck	14,389	17.92%	2,579	2.17%	56
4/20/20	HubSpot	Multiple	Webinar	WE3 - Simplifying Complexities	How much longer can your clients’ policies tolerate this economic turbulence?	15,515	12.18%	1,890	0.85%	16
4/23/20	HubSpot	Multiple	Webinar	WE1 - Protect Your Time	What does it mean to ‘manage by exception’?	21,756	11.68%	2,541	1.22%	31
4/29/20	HubSpot	Multiple	Webinar	WE2 - Protect Your Time	How can you protect your time during the COVID-19 crisis?	21,432	10.05%	2,154	1.21%	26
5/4/20	HubSpot	Multiple	Webinar	WE3 - Protect Your Time	[Live Webinar] Protect Your Time - Manage by Exception	21,572	9.19%	1,982	1.36%	27
5/7/20	HubSpot	Multiple	White Paper	WP - COVID-19	COVID-19 and the Life Insurance Industry	21,138	10.80%	2,282	12.31%	281

[SirJJAnderson]

I could also share pbix file if that would be better? You'll just have to tell me how to share it lol

[nandukrishnavs]

@SirJJAnderson share the pbix file via google drive/dropbox

[SirJJAnderson]

@nandukrishnavs 

Here is the pbix file: https://drive.google.com/file/d/1sOB7DeY0RdNmDcI-5vmy3VzO3KKPhYF5/view?usp=sharing

And here's the excel table: https://drive.google.com/file/d/12Vo6MdW-ObIcMmModtJ0YjWS5jGxjvab/view?usp=sharing