Need help for Dynamic Column Total Percentage

Hi Anonymous 

Sure thing

Let's say I have 2 tables called "Consolidation" and "Product Table"

These tables have a many-to-one type of relationship on the "SKU" column. Please see attached links for my sample data.

Consolidation.csv

Product Table.csv

What I'm trying to do is to find a dynamic denominator value for the Column Total Percentage, which I unfortunately don't know how to do it.

I wrote this DAX and it only returns a static value which is "176" for Collection 23 as you saw in the screenshot.

VAR ColumnTotal =
    CALCULATE(
        CALCULATE(
            DISTINCTCOUNT('Product Table'[STYLE]),
            FILTER(
                Consolidation,
                Consolidation[Sales_Type] = "Actual" &&
                Consolidation[Sales Period] = "Full Price Period" &&
                Consolidation[COLLECTION_NUM] > 22
            ),
            FILTER(
                'Product Table',
                'Product Table'[CATEGORY] <> BLANK() &&
                'Product Table'[COLLECTION_NUM] > 22
            )
        ),
        REMOVEFILTERS(Consolidation),
        FILTER(
            VALUES(Consolidation[COLLECTION_NUM]),
            Consolidation[COLLECTION_NUM] > 22
        )
    )

The number is correct in context of the whole collection.

But if the users only want to see specific category and use the filter pane, it doesn't change accordingly.

For example the Users filter on "Bags, Dress, Skirt"

I want it to do something like

This number makes more sense because it is not possible to produce 176 of different style only for 3 kind of products

Hopefully it is more understandable