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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
limewire
Helper I
Helper I

How to use filtered data to create new values using values from the same column

Greetings!!

 

I have a crosstab with 4 descriptive columns and ~10 columns with data. Basically, I want to create a query or table that would this crosstab but filtered so that only certain elements from the 4 descriptive columns are used.

 

The 4 descriptive columns are Medium, Category, Program/Network, Variable

 

THEN (this is where things get fuzzy/confusing)

I want to create new columns with this Total Viewer Values figure (or use the formula to replace the data values in the columns with data). This Total Viewer Values formula column would depend on the original crosstab so that when the query is appended it would also use the forumla to append the new data.

 

Is this possible? Or are multiple queries needed?

 

This is my general outline/Idea

From Medium Column 

list 1.PNG

 

From Categories Columnlist 2.PNG

 

 

From Program/Network Column

All elements EXPECT those that include "View Regularly" at the end

 

From Variable Column

Weighted

 

Each Program/Network will have a row for each Category across my ~10 data columns

 

Total Viewer Values is perhaps the trickiest part. It is category(view regularly/occasionally) divided by category (can be any of the filtered ones expect for view regularly/occasionally)

 

In serious need of some assistance on how to exactly tackle this.

Any and all help would be greatly appreciated. 

5 REPLIES 5
parry2k
Super User
Super User

@limewire I'm having hard time understanding your post, sorry it could be me. May be if you can share example data wiht expected result, it will help.

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k 

 

Medium | Category | Program/Network

I want a way to get a value (I want to call this value "Total Viewer Values" but if it does not need a name that is fine as well) that uses 2 different Category elements. 

 

Total Viewer Values =

Medium | Category a (see picture) | Program/Network (example program "My So Called Life") / category x (view regularly/occassionaly (net))

Medium | Category b (see picture) | Program/Network (example program "My So Called Life") / category x (view regularly/occassionaly (net))

Medium | Category c (see picture) | program/network (example program "My So Called Life") / category x (view regularly/occassionaly (net))

Medium | Category d (see picture) | program/network (example program "My So Called Life") / category x (view regularly/occassionaly (net))

Medium | Category e (see picture) | program/network (example program "My So Called Life") / category x (view regularly/occassionaly (net))

 

I have ~10 columns of data. Is there a way to have the columns of data for category a,b,c,d,e / category x ? And is there a way to have this set so that if data is appended, it still works?

 

Does this help? (and thank you for your help!!)

 

Inkedlist 2_LI.jpg

 

@limewire ok thanks for explaining. Is the task to add values for cat (a,b,c,d,e) and then divide by x?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k  Seriously, thank you so much for your assistance and patience. 

 

The values for cat (a,b,c,d,e) are all to be separately evaluated and not added together but yes! the goal is to divide those by x!

 

I am now thinking that perhaps a measure is best? I've referenced 2 queries from my original data source, one with the values for cat(a,b,c,d,e) filtered for and the other filtered for values for cat x. There are more values for cat(a,b,c,d,e) than there are for cat x so I have the table relationships set to one-to-many (I have no idea what I am doing and if I am even on track). 

 

I'm still lost in the struggle. More than willing to start over and go in whatever direction you are leaning towards. 

 

@limewire i'm having hard time actually understanding the problem. If you are ok, send me a PM, and we can connect offline and chat/speak about it.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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