Question: use the detailed family report and show the family size for each id

[CAOLU10]

Hi Team,

I have a request that I have the original family report with detailed relations to the related ID. the final results I would like to have in Power BI is to show the total ID with the different family size by chart. Could someone help me how to make it?

I attached an sample for dummy data: the original data are the details family data with mutiple ID, I would like to create a new table with single ID plus the related number of familys and then use the different family size as the row and count the ID.  the final chart is like the last one.  I am not sure if there is any direct way to the chart without cover the table.

Thank you,

ID	Name	Relations
1234	ABC	Daughter
1234	ABC	Son
1234	ABC	Daughter
2345	CDA	Son
1245	ACD	Son
2341	AAD	Son
1245	ACD	Son
1245	ACD	Son
2341	AAD	Son
2341	AAD	Daughter
1234	ABC	Spouse

[v-yangliu-msft · Accepted solution]

Thanks for the reply from Ashish_Mathur  and danextian , please allow me to add some more information:
Hi  @CAOLU10 ,

Here are the steps you can follow：

If you don't want to create a table for the effect, you can just create a calculated column:

Count of Records =
COUNTX(
    FILTER('Table',[ID]=EARLIER('Table'[ID])),[Relations])

Place [ID] to Y-axis and set it to Count(Distinct), place [Count of Records] to X-axis.

Set X-axis - Categorical

If you want to create a calculation table to achieve the effect, consider the following steps:

Create calculated table.

Table 2 =
SUMMARIZE(
    'Table',
    [ID],"Count of Relations",COUNTX('Table',[Relations]))

Place [ID] to Y-axis and set it to Count, place [Count of Records] to X-axis.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[CAOLU10]

Hi Liu yang, thanks for your detailed instruction and I think this solution is the better one that I do not need to create the new table and functions. thank you

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[CAOLU10]

Hi @danextian  

Thank you so much for your quick response, this helps me a lots. I will try it and thanks also for the BI file which is easier for me to see the details. 

Lu

[danextian]

Hi @CAOLU10 

The first table would be as simple as counting the rows in the table.

Count of Records = 
COUNTROWS ( 'Table' )

Please see the attached pbix.

For the second table, you can create a calculated table to count the rows for each ID.

Family Size = 
-- Calculate the family size by counting the number of rows
-- where the ID column in the 'Table' matches the current row's ID
COUNTROWS ( 
    FILTER ( 
        'Table', 
        'Table'[ID] = EARLIER ( 'Table'[ID] ) 
    ) 
)

And then a measure to count the distinct IDs

Distinct ID = 
DISTINCTCOUNT ( 'Table'[ID] )

Add the family size column and Distinct ID measure to the visual.