Matrix with Measures and unique columns values

[Yoshimitsu411]

Hello

I am trying to create a matrix as in the following diagram where the values under "Actual" and "Budget" for each month are Measure values - Actual Revenue and Budget Revenue. But in the row I need to display the name of the measure category which is Revenue.

How do I display the custom measure category name (not the measure itself) in the row?

Thank you for any help.

Yoshi

[amitchandak]

@Yoshimitsu411 

Matrix has option "Show on rows", that will move you measure on the first column. But this input needs a bit of transformation

https://www.burningsuit.co.uk/blog/2019/04/7-secrets-of-the-matrix-visual/

Something like this. create a new table and use it

New Table=
union(
summarize (Table,Table[Month],"Name","Actual","Value",[Actual Revenue]),
summarize (Table,Table[Month],"Name","Budget","Value",[Budget Revenue])
)

[Yoshimitsu411]

Hi @amitchandak 

Thanks for helping me.

I created the following custom table.

SUMMARIZE('Calendar',

    'Calendar'[Month],

    "Metric", "Revenue",

    "Actual", [MTD Revenue Actuals],

    "Budget", [MTD Revenue Budget]

    )

After creating this, I can see the table in the correct format however the value of my measures MTD Revenue Actuals and Budget are incorrect. It seems Summarize is changing the values. Also I am not able to get the Months to sort correctly.

Thanks

[amitchandak]

@Yoshimitsu411 , You are not using year or month year. Assuming you have only one year of data.

Can you share sample data and sample output.

[Yoshimitsu411]

Thanks for your reply @amitchandak .

My matrix currently looks like this.

What I want in the matrix is in the image below, with the addition of the Actual and Budget sub columns under each month and the measure names on the side.

The idea is to compare the Actual and Budget for each month for each measure.

I have two fact tables (A and B) with unpivoted columns for Actual Month and Actuals and Budget Month and Budget.

I have one Calendar table with Month column (Jan, Feb, Mar....) and Sequence column (1, 2, 3...) used to sort the month column.

Calendar month is one to many with the month column in the fact tables.

I have 5 measures that calculate sum and percentages etc.

Thanks again.

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[Yoshimitsu411 · Accepted solution]

Thank you for all your replies.

This has now been solved.

[Icey]

Hi @Yoshimitsu411 ,

Glad to hear that. You may help accept the solution above. Your contribution is highly appreciated.

Best Regards,

Icey