PowerPivot - Calculate/Count Unique Row Across Multiple Tables

excellll

New Member
Joined
Jan 2, 2013
Messages
5
Hi,

My model is based on the structure below -with the fact table being the link between the peoples and country table.

I am trying to count the number of unique People, which i have accomplished using the countrows statement. =countrows((DISTINCT(People[Name])))

My problem arises when the I try to analyse the number of unique people by Country_Name - The total number of records appear for each person and the formula does not split the total.

Is there a way for the calculated field to work across multiple tables?

People</SPAN>
Fact</SPAN>
Country</SPAN>
ID</SPAN>
Sales</SPAN>
ID </SPAN>
Name</SPAN>
Revenue</SPAN>
Name</SPAN>
People</SPAN>
Country</SPAN>

<TBODY>
</TBODY>


Many Thanks
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Hello, welcome to the board!

Basically what it sounds like is you want to count by name and by country. If this is the case, why not just pivot the data? Create relationships between people and country, then when pivot the data it brings it all together. This is one of the benefits of PPV. Why re-create the wheel?

Also, in regards to your model, doing it this way would allow a person to be shown from multiple countries. Is that how you want it? To me it would make sense to have all data about people in one normalized table, to include their country, or other personal data.
 
Upvote 0
Thanks for the reply.

I will have a try with the link table.

People and Country were bad examples!!! Would have been better if i said A Linked to B and A Linked to C. Create a measure on B and analyse the data based on data from C.

I am trying to replicate a Qlikview document within PPV, which allows measures to be analysed/filtered based on secondary and tertiary linked tables, which is what i was trying to achieve.

From what i understand, only measures on primary linked tables can be analysed (A Linked to B)
 
Upvote 0
you can do magic with Powerpivot and I'm pretty sure that when I understand what you're trying to achieve we'll make it right :) but as of right now, I don't have a clear idea of what you're trying to achieve :confused:
Do you think that you could perhaps upload the workbook and give us an example of how would you like the results to be shown?

Best,
Miguel
 
Upvote 0
Hi excellll,
I had a look at the attached Excel and the PivotTable built with it. What you are trying to do is to display a pivot table that has the market, the total cost of products in that market, the total revenue and distinct count of products in the market.

Here is one way to solve it. There might be other easier ways to solve this problem that I am not aware of:

You have to establish a many-to-many table to act as the bridge between products and markets. I have created a table called MarketProduct which has the unique combination of markets and products from your Fact table. Then, I created a measure with the following formula:

[Product Count] = calculate((DISTINCTCOUNT(product[_product_area])),'ProductMarket')

And here is a picture of the pivot table - please note that I have added a few test records and changed the values of some cost and revenues to make debugging easier.

https://www.dropbox.com/s/mrufieq5fazkn2o/Count Rows in many2many.png

Here is a picture of the model with the new table added to solve the many2many issue:

https://www.dropbox.com/s/5bme5pqehhiyywb/Count Rows in many2many model.png

And here is the original XL file with the changes I have done - Please note that I have renamed tables to make it meaningful for me to understand them (I can't work with table1, table2, etc.)

https://www.dropbox.com/s/lv73c3h4phkyd8x/Count Rows across different tables.xlsx

To understand how this solution works, please take a look at
Resolving Many to Many relationships leveraging DAX Cross Table Filtering « Gerhard Brueckl's BI Blog
 
Last edited:
Upvote 0
Elegant!

What I was actually gonna come up with was to actually just drag the Product field from the Fact table and just make it a distinct count (that only if what you were aiming at is what Siraj came up with).

You can make it a distinct count by just right clicking on the field once it's on the values field of the Powerpivot field list.

Great approach, Siraj!
 
Upvote 0
Hi Miguel, thanks a lot for your kind words!

I just wanted to understand what you are saying better as it does not produce the right results for me. In the pivot table that I have given in the first screenshot, if I remove the measure and add the _PRODUCT column from Product table and change the summarize by to DISTINCTCOUNT, then I should get the same numbers as in the pivot table with measure - is this what you are saying?

If I do this, all I get is 8 for all the markets and the total (from what I understand, PowerPivot has to be told about the bridge table. Otherwisse, it ignores it. That is why I have created a custom measure) - please correct me if I am wrong.
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,700
Members
448,293
Latest member
jin kazuya

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top