Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

filter a table by columns and perform calculation on new table

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

 

Capture.PNG

 

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:

 

Capture.PNG

 

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

 

Capture.PNG

 

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?

 

 

2 REPLIES 2
v-piga-msft
Resident Rockstar
Resident Rockstar

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

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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

 

 

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.