Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
pnem
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. 

pnem_0-1669198169311.png


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

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

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

View solution in original post

4 REPLIES 4
daXtreme
Solution Sage
Solution Sage

daXtreme_0-1669206143341.png

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

pnem
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!

OdetaJ
Frequent Visitor

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

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.