Subcategory as % of category power bi

[pnem]

Hello, 

I would like to calculate saldo subcategory as  share in category.

Here is an example of data and what results I want to get. 

Green column is what I expect as a result, Red column is what I get from dax formula

Treid something like this : 

%ofSubCat =
DIVIDE(
CALCULATE(sum([Saldo]),
ALLEXCEPT(Ftable,[SubCat])),
CALCULATE(sum([Saldo]),ALLEXCEPT(dimCat,dimCat[Category])))

but this is showing me uncorrect data. 

[daXtreme]

%ofSubCat = 
var VisibleCategories = DISTINCT( T[Category] )
return
if(
    and(
        NOT ISINSCOPE( T[Subcategory] ),
        ISINSCOPE( T[Category] )
    ),
    SUMX(
        VisibleCategories,
        CALCULATE(
        
            var SubcategoriesVisible = 
                DISTINCT( T[Subcategory] )
            var SubcategoriesVisibleCount =
                COUNTROWS( SubcategoriesVisible )
            var SubcategoriesVisibleWithSaldoCount =
                COUNTROWS(
                    FILTER(
                        SubcategoriesVisible,
                        [Total Saldo] = 1
                    )
                )
            var Result =
                DIVIDE(
                    SubcategoriesVisibleWithSaldoCount,
                    SubcategoriesVisibleCount
                )
            return
                Result,
                
            ALLSELECTED( T[Subcategory] )
        )
    )
)

[OdetaJ]

Hi @pnem,

Could you please tell me how you want to calculate the "%ofSub"? Do you only count the times "Saldo" is some value for the subcategory? This is why for Category 1:

%ofSub = number of times Saldo = 1 divided by number of subcategories in a Category 1  = 2/3,

or do you want to sum up "Saldo" and divide it by the number of subcategories in a specific category? In your example, the result is the same, but if you have other "Saldo" values than 1, you will get a different result and need to use a different formula.

[pnem]

Hello @OdetaJ I am having only value 1 for Saldo, and goal is to devide number of Saldo's with one with number of subcategories in each category.
Hope you understand the idea, thanks!

[OdetaJ · Accepted solution]

% of SubCat = 
VAR SubcatCount = DISTINCTCOUNT(Categories[Subcategory])
RETURN
    IF( NOT ISINSCOPE(Categories[Subcategory]) && ISINSCOPE(Categories[Category]),
        DIVIDE( SUM('Fact'[Saldo]), SubcatCount )
    )

Also, the formula can change if you use filters and/or slicers.