Forum Discussion

JLdH's avatar
JLdH
New Member
9 years ago

Subtract values from same category but from different subcategories, show total on category level

Dear All,   I'm trying to subtract/compare two values from different subcategories in a kind of subtotal for that category. However, I don't want to use the standard subtotal of the matrix view, as...
Need Help
Baskar's avatar
Baskar
Icon for Resident Rockstar rankResident Rockstar
9 years ago

We have to create two calculated column in Table1 (3)

 

1. To get value for type of subcategory.

 

Sub_Category_value = Calculated(Sum(KoersenTbl[Value]))

 

2.  For this minus the sub_Category total from Category Total

 

Sub_Category_value_from_category_Total =

var Cur_category = 'Table1 (3)'[FundName] 

var Cur_Sub_Category_value  = Sub_Category_value

 

Return   calculate ( sum(Sub_Category_value),filter ( all(Table1 (3)) ,'Table1 (3)'[FundName]  =Cur_category ) ) -

Cur_Sub_Category_value  

 

 

Try this, check the  bold before execute. don't forgot it is calculated column in Table1 (3).

 

 

let me know any help

 

 

JLdH's avatar
JLdH
New Member
9 years ago

tried it and it shows a result, but not the expected one :) (even if I filter out dates), it still shows two rows.

 

 

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi JLdH,

     

    Suppose the NAVTodaybis is a measure used to sum total values for each subcategary

    NAVTodaybis =
CALCULATE (
    SUM ( KoersenTbl[Value] ),
    ALLEXCEPT ( 'Table1 (3)', 'Table1 (3)'[Title] )
)

    Based on that scenario, please create below measure and add it to matrix visual.

    Subtract =
    IF (
        LASTNONBLANK ( 'Table1 (3)'[Title], 1 ) = "C+F",
        BLANK (),
        ABS (
            [NAVTodaybis]
                - (
                    CALCULATE (
                        SUM ( KoersenTbl[Value] ),
                        ALLEXCEPT ( 'Table1 (3)', 'Table1 (3)'[FundName] )
                    )
                        - CALCULATE (
                            SUM ( KoersenTbl[Value] ),
                            ALLEXCEPT ( 'Table1 (3)', 'Table1 (3)'[Title] )
                        )
                )
        )
    )

    Output result.

     

    Thanks,
    Yuliana Gu