Date data by categories - across multiple categories

[bestefa01]

Hi all

New user here, and I would like to request advice on how to organize my data.   I have two tables (from excel): First table is investment level by date across certain bank accounts:

Table1

Second table are the categories I want to be able to select and visualize across:

Table 2

I'm really struggling with how to organize the data into the categories in Table 2.

My first attempt was to just use Table 1, and I used measures to create different categories - so for example a "Liquid" measure which summed the first two columns.  And also an "Equity" measure which summed all of the Equity columns.  But it didn't seem to work well - when I clicked on Equity on one viz tabel it did not flow through to the other cuts of data I did.

With all the flexibility of PBI, I'm not sure how best to organize: should I use grouping, measures, new calculated columns, data relationships across these two tables, or something else? 

ta!

[Anonymous · Accepted solution]

Hi @bestefa01  ,

You probably want to unpivot the Table1 columns to be able to show all in one visual:

> Go to "Edit Queries"

> Select all columns in Table1 except for Dates
> Go to "Transform" Tab
> Select "Unpivot Columns"

Hope that helps,
Robin

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