3 components in one field

[bcoro]

I received one table that has thousands of rows and over a thousand columns. My issue is one field represents 3 different components. [familyunder18elementary] family represents the household type (family with children, adults no children, single person), under18 represents the age grouping (under 18, 18-25, 26-44...etc.) and the highest level of education (no school, elementary, high school, college). I want to be able to create categorical filters/parameters whatever is easiest to know how many are in elementary total regardless of household and age. Also, I want to be able to count all families with children under 18. I just don't know how best to tackle this massive table. 

code	famillyunder18elementary	singleover64noschool	familypovertyrenting
1	37	72	15
2	87	95	70
3	46	84	13

[v-mengmli-msft · Accepted solution]

Hi @bcoro ,

Based on your description, you want to calculate the total number of elementary education level and the total number of households where children are under the age of 18. I don't see the other subcategories from the table you provided, there may be such a problem that a single person with a primary education level may be counted twice in two fields, which is difficult to separate out the number of people with all elementary education levels.

Are there columns in the table for subcategories? If there were, the problem would be much simpler. You can get the value you want with a simple aggregation function.

Measure 1 = SUM(Table[famillywithchildrenunder18])

Measure 2 = SUM(Table[elementary])

If you don't have the columns in the example above in your table, I recommend that you consider using the difference to get the results you want. For example, I want to get the number of families with children under the age of 18.

Measure 3 = SUM(Table[familywithchildren])-SUM(Table[familywithchildrenabove18])

If the above is not what you want, I suggest you provide more information, such as your PBIX file. How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Best regards,

Mengmeng Li