filter a table by columns and perform calculation on new table

[Anonymous]

I have a wide table showing prices,  500 columns, and 10k rows:

I want to create a slicer based on filters so that only a few columns are selected (ie, only columns A,B,C in this case)

I then want to create a new table based on this table, and compute an even split between them (in this case, 1/3 of each, in general will be 1/(number of filtered companies))

to get something like this:

and then develop a new table depending on these values, ie:

I can do this in excel, VBA, R, or python, but not sure how to implement this in power bi.

The steps I would follow are:

- select companies interested in

- filter table to only include columns of those companies

- create new table based on even split

- calculate daily change based on daily values

Is there any suggestions on how I can do this in power bi?

[v-piga-msft]

Hi @Anonymous ,

I still have a little confused about your logic.

If I understand your scenario correctly that you have a table with an Index column and A,B,C column like your first image.

When you select from the slicer, it will return the table for your second image.

So what is the logic from the second image to the third image?

If it is convenient, could you describe your logic in more details so that I can understand your scenario better?

Best  Regards,

Cherry

[Anonymous]

Hi @v-piga-msft 

thanks for your reply.

My logic is 

- I start wtih 500 columns showing historic stock prices for each company (A,B,C,......ZZZZ)

- I want to be able to create a slicer to view only a specific subset of companies (A,B,C) as in the first image

- Based on this subset of companies, I want to create a new table where I buy their stock equally on day 1. ie I have $100, and want to split it equally between A, B, and C, and end up buying 0.95 shares of A, etc, as in image 2. This is based on the stock value on day 1

- Starting from the initial set of shares, I want to compute the total value in each of the following days, based on the number of shares I have. Based on the number of shares I have from day 1, on day 2 and the value of the shares, I have a total value of $140.45, day 3 is $236.91, etc., as in image 3