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.

Announcements

Power BI T-Shirt Design Challenge 2023

Submit your creative T-shirt design ideas starting March 7 through March 21, 2023.

Power BI March 2023 Update

Find out more about the March 2023 update.

March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors
Top Kudoed Authors