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.