I don't even know how to formulate my question

[Anonymous]

Hello community,

I'm having difficulties formulating my problem in a form to find the answer so I'll try to explain what I need help with.

I the following table:

Id	Category	Sub-category	Sub-sub-category	Count
1	A	AA	AAA	1
2	A	AB	ABA	2
3	B	BA	BAA	1

This table is simplified and also contains +100 million rows.

I have two slicers for Sub-category and Sub-Sub-category. After I apply the slicers in the following configuration: Sub-category: AA and Sub-Sub-Category: AAA I want to be able to calculate the share AAA count accounts for of the Category A. In this case AAA 33%.

The only way I have been able to solve this is to duplicate the large table and create a relationship between Category columns. That way I can obtain the total count from secondary table. But due to the size of the table it is sluggish and heavy. Is there any way to solve this with a measure?

Thanks!

[jppv20 · Accepted solution]

Hi @Anonymous ,

You can try this measure:

% of category =

var subsubcategory = CALCULATE(SUM('Table'[Count]),ALLEXCEPT('Table','Table'[Sub-sub-category]))

var category = CALCULATE(SUM('Table'[Count]),ALLEXCEPT('Table','Table'[Category]))

Return

DIVIDE(subsubcategory,category,BLANK())

Result:

If I answered your question, please mark it as a solution to help other members find it more quickly.

[Anonymous]

This did the trick. Thank you for the quick reply.

[AllisonKennedy]

@Anonymous  Do you have a star schema and Dim table for Product? The Dim table can contain Category, Sub-category and sub-subcategory, but should not contain the counts. 

https://excelwithallison.blogspot.com/2020/08/its-complicated-relationships-in-power.html 

Category Subtotal Sales =

CALCULATE(SUM(FactInternetSales[SalesAmount]), ALLEXCEPT(DimProduct, DimProduct[Category]))

[Anonymous]

Yeah it is a star schema. The count column is a measure. I just wanted to simplify.

Thank you for the quick reply.