Pivot table data reporting problem

[Burrgogi]

I wanted an easy way to keep track of all the games that own so I created a spreadsheet. Here is a small sample.

col. A = Game Title
col. B = Library 1
col. C = Library 2
col. D = Library 3

Some games I have just on Steam, see row #2.
While some games I own on multiple services GoG, HB & Steam. See row # 8.

Instead of using column filters, I decided to create a pivot table on a separate worksheet that shows all the games I own just on Steam alone. I'm not getting the expected results. Not sure what I'm doing wrong.

I've tried various combinations such as moving the Library 1, 2, & 3 fields over to the Column Labels but it's not working.

 

[Misca]

Start by turning your data into a proper Excel table ( = select a single cell and use the shortcut Ctrl + T ). This way your data expands when you add new rows / columns to your data.

Once you've turned your data into a table use Power Query ( Power Query is a free add-in for Excel 2010-2013 but built-in in later versions ) to unpivot your data ( select only the Title column and use "Unpivot other columns" just to make sure your table can be expanded with new columns if needed. For this you might want to delete the "Change type" -step Power Query likes to add to your query automatically. ) into a correct data structure. What you're after is a single column for game titles and a single column for your services. The Library 1 etc. doesn't seem to add any value to your data so you can remove the Attribute column you're going to get when you unpivot your data.

When you have turned your data into a proper structure you can close your Power Query. Just make sure you load your data into a connection only + check the "Add this data to the Data Model" so you can use the Power Pivot instead of the old-fashioned pivot tables:

Also, you might want to add another column for the Version of the game: I'm not familiar with games but to me it looks like Bioshock 1 is basically the same game as Bioshock 1 Remastered Version. Having another column for the different versions makes sense if you want to count both versions as a single game instead of two games as it is stated in your current data.

Another thing you might want to do is make sure you've unchecked the Enable Background Refresh from your query properties ( = right click the query name in your Queries & Connection window + click on the Properties ).

If you don't see a Power Pivot tab in your Excel ribbon right click on any of the current tabs in your ribbon and select "Customize the Ribbon" and make sure the Power Pivot is checked.

When you have your data in your Data Model you can start adding the DAX measures. I'm thinking something like Games Count ( = count of unique game titles ) and Libraries ( = number of libraries the same game is found on ):

Games Count:=DISTINCTCOUNT(Games[Title])

Libraries:=COUNTROWS(Games)

Make sure the Table / Column names match with the table / column names of your data. Here's what my Games table looks in Power Query: