Building a summary table from measures and existing tables?

[Anonymous]

Hi Everyone

I want to present a table of data based on existing measures and columns that presents a summary of the existing data. This will need to change dynamically dependent on the team slicer selection. I can't work out a way so far - it seems like I need to create new rows and columns? I have included a screenshot mock-up I made in excel to demonstrate what I have, and the desired result.

thanks!

[v-lionel-msft]

Hi @Anonymous ,

1. Transform the data in 'Edit Query'.

2. Create a measure to calculate the value of the matrix.

Please refer to my .pbix.

Best regards,
Lionel Chen

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

[Anonymous]

Hi Lionel

Thank you for your help. The use of a measure was in the values field is interesting. Your solution would be great if i had not provided a simplified version of my requirements initially.

My report includes time intelligence functions already, so there are report level filters that restrict our figures to a specific time frame. I think this is affecting the measure you wrote, as when I attempt to recreate it, I get no variance column between budget and actual. Could you suggest a solution?

[v-lionel-msft]

Hi @Anonymous ,

Has your problem been solved?

Best regards,
Lionel Chen

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

[v-lionel-msft]

Hi @Anonymous ,

"I get no variance column between budget and actual."

The [Variance] column is actually the column subtotals label of the matrix. Did you find this？If yes, it may indeed be that the report level filter affects the return value of the measure.

Would you mind sharing your .pbix file? Or give us a sample data with date column. Also please let me see the results you made.

Best regards,
Lionel Chen

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

[parry2k]

@Anonymous  In this case unpivot your data and split attribute into two columns, from there everyhting will be super easy

- transform data
- select team column 
- right-click, unpivot other columns it will add two columns, attribute, and value 

- split attribute into two columns by space, and rename as per your requirement, let's its called category and type (type is actual and budget) and type is (hours, revenue cost, margin)

- close and apply

To visualize,
- matrix visual:
- add team and type on row
- add a category on columns
- add value on values section

if you drill down to the next level in the rows, it will get what you are looking for. You can add slicer on the team to filter for a specific team. From here onwards everything will be super easy.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡