I have survey data that is weighted and includes "dummy" records that are only used when calculating Country of Residence and Port of Customs (all other columns are blank). I need to display information from other columns but exclude these "dummy" records from my counts and sum of weights. How do I summarize/group my data to exclude them? For Example: I have a column for Purpose of Trip it contains codes 1=Business, 2=Convention, 3=Vacation, 4=Visit Friends/Relatives, and 5=Other Purpose. There is also a code 0 meaning no answer. I would like to create a table that contains the row count for each code 1-5 and a sum of weights for the same codes. I am very new to Power BI and am getting confused on how to create this from my one fact table. I am trying to get an example together by Friday (USA PDT) to show to management. This type of table would be used for several of my survey questions...Please help!
Hi Laura,
Welcome to our forum!
It's almost impossible to keep in mind all of the details you covered in order to form a picture of your data in my mind and scenario. By the time I'm into the third sentence I've forgotten the first. Please share a sample file in Excel using some dummy data if necessary. You can also show your desired result so I can be sure I understand what you're trying to do.
Thanks,
Mynda
I have included sample Excel data. The column with GrpPrpMain values is what I am concerned with. Do I leave the values as null or convert null to zero and not include somehow? This issue is one that I will have for all but two variables. So once you help me get this fixed I can move on an apply it to several other question setups.
I will probably contact the group with another question related to my data setup and whether I should to my modeling in Excel and importing that to Power BI or just doing it Power BI. I have learned quite a bit from your training videos, so thank you! Anyway, I should stop rambling...
Hi Laura,
Thanks for the file. That makes it much clearer. It doesn't matter if the field is null or zero, you can use the filters on the visual, report, page etc. to filter out those records from the results. See the lesson 4.08 of the Power BI course on filtering.
I recommend building your report completely in Power BI rather than modelling in Excel and then exporting to Power BI.
Mynda
Thank you! I will take a look and review Lesson 4.08 for filtering (I may not have gotten that far in the training yet). Once I get that fixed I will have another question for you on how to do a multiple response question that still does cross report filtering...
No worries. It's a good idea to completely finish the courses before you try to build your own reports 😉
In a perfect world I would agree with you wholeheartedly. Thanks for you patience with my newbee questions...
