cancel
Showing results for
Did you mean: Frequent Visitor

## Subcategory as % of category power bi

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.

1 ACCEPTED SOLUTION Frequent Visitor
`% 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.

4 REPLIES 4  Super User ``````%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] )
)
)
)`````` Frequent Visitor

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. Frequent Visitor

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! Frequent Visitor
`% 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.  