cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
Solution Sage

``````%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.

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors