Forum Discussion
Data grouping improperly such that filtering on dashboard is only partially working
I'm struggling to get the grouping right for my dashboard with respect to a Publisher pie chart visualization. Essentially, on my Dashboard, I have the pie chart which will (other than date slicers) only ever be filtered to include or exclude Sequential Art ("SA" or "Seq Art").
In Power Query, I added a column to my source data checking if the book was SA. Then I grouped the data by Parent Publisher and SA and added the Pct of Total and Pub Grping columns (Pub Grping simply groups any publishers that make up less than 5% of the total into the "Other (<5%)" bucket to keep the visualization cleaner.
From here, I grouped the table again by publisher and redid the Pct of Total and Pub Grping columns to ensure that my categorizations were accurate when SA was included.
In PowerBi, I've mapped the queries (*:* - Source Parent Publisher: Query 1 Parent Publisher) and (*:1 - Query 1 Parent Publisher: Query 2 Parent Publisher) think that now if I filtered out SA on my dashboard, Hitotsubashi Group would go away entirely (which it does) and Indie would go from 12/136 to 11/115 (8.82% to 9.57%) which does not happen. Instead, my visualization shows 10.34% (12/116).
I'm all but certain my issue lies with how I did my groupings, but I'm not sure what I should have done differently. I'm very new to Power BI and Power Query and am messing around with personal data while I wait to gain access to company data.
6 Replies
- HotChilli
Community Champion
It's a bit challenging to read through that and identify the exact problem. I would really need to look at what measures are in the pie chart and what's in the slicers. Can you link the pbix and I'll have a look?
-
I'm a little concerned at the description of the model (I take it that *:* means many to many - which we don't like) and the names of the tables implies that they are not business-like entities but different versions of the same query
I'll try to rephrase and summarize the problem. The Indie publisher should go from 12 records to 11 when SA is filtered out.
- HotChilli
You'll have to post the pbix on a 3rd party site and post the link here. (onedrive, box etc)
Thanks, is it that clear I have no idea what I'm doing? lol
Appreciate the help. I'm specifically looking at the V2 page. V1 was effectively what I wanted but I succeded with bookmarks (which I accidently broke) and DAX. Page V3 is me continuing to play around with it without breaking something further.
- HotChilli
Don't worry, There is a steep learning curve involved.
--
I am going to stand by my original comments in my first reply. The model needs rework.
I'm not going to go into detail about why the wrong figure is shown on the pie chart but here's it roughly. The 'seq art' slicer is from the Reading Tracker table. When 'No' is selected, all the publishers for those records are retrieved then jumping through two relationships to get the figures from Pb House Distr Total (add up all the grpd count except Hitotsubashi makes 116).
In this scenario, Reading Tracker is used as a dimension table to filter the summary table. Now I don't know what the requirements for your dashboard are (apart from a learning exercise) but you wouldn't normally do this. You would use the Reading Tracker as the Fact table and slice and dice with dimension tables (probably Publisher or Source or Genre or Seq art or Date related 1 to many to the fact table) . The measures would recalculate according to slicer choices rather than pre-calculating totals in a summary table and reverse slicing through a many to many relationship.
--
Sometimes a summary table is used in a model (but not 2 versions of the same table) .
So, dive in, it's all good experience. I'm not going to spend time writing a measure or anything to get you further because I'll always end up saying "I wouldn't do it this way" .
Hope it helps
HotChilli wrote:In this scenario, Reading Tracker is used as a dimension table to filter the summary table. Now I don't know what the requirements for your dashboard are (apart from a learning exercise) but you wouldn't normally do this. You would use the Reading Tracker as the Fact table and slice and dice with dimension tables (probably Publisher or Source or Genre or Seq art or Date related 1 to many to the fact table). The measures would recalculate according to slicer choices rather than pre-calculating totals in a summary table and reverse slicing through a many to many relationship.
So, dive in, it's all good experience. I'm not going to spend time writing a measure or anything to get you further because I'll always end up saying "I wouldn't do it this way" .
Hope it helps
I need to look up Dimension and Fact tables to fully understand what you're saying but I think I get the jist: I'm putting the cart before the horse and it's making it more complex than it needs to be.
I appreciate the help and you taking a look - there are no requirments other than learning so I have zero problem deleting all of the extraneous tables and going back to square one.
