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
Resident Rockstar
9 years ago

Do u want substract the sub-category value from total category value am i right?

 

if yes share some sample data i will help u .

JLdH's avatar
JLdH
New Member
9 years ago

Hi Baskar,

 

I'd like to subtract two sub categories and make that the result, without using matrix automatic subtotals or show subcalculations.

But looking at your answer, showing the sub-category from the total could maybe work as well.

 

All values are in the same table (KoersenTbl)

 

Those are benchmark values (name=index column):

 

 

Those are fund values (name=index):

 

this is the categories table (Table1 (3)), Title=subcategory name, BenchFund= type of subcategory, FundName= main category:

 

 

This is the data model (index = title and are linked):

 

  • Baskar's avatar
    Baskar
    Resident 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
        Microsoft 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