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

[JLdH]

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 it shows the calculations steps.

Maybe with a screenshot 🙂 :

So I'm comparing the value of a fund (C+F Belgian Growth Cap), with its benchmark (BELM). The fund and the benchmark are being defined as different subcategories ("title") from one category ("fundname", here CFBG), so I'm able to filter them and make measures that totals them individually.

When I try to subtract the two (filtered) measures in a new measure (TestMeasure), it shows the subcalculation filtered and then makes a (sub)total on the bottom of the matrix. (logical 😉 )

Two measures (it's testing, so table names are not very friendly) that are subtracted in TestMeasure:

QuoteFund = calculate(sum(KoersenTbl[Value]);filter('Table1 (3)';'Table1 (3)'[BenchFund]="Fund"))

QuoteBench = calculate(sum(KoersenTbl[Value]);filter('Table1 (3)';'Table1 (3)'[BenchFund]="Bench"))

TestMeasure = [QuoteFund]-[QuoteBench]

(there are some date calculations as well, but I'm leaving this behind here as the point is to always have the latest available, that works already)

What I'd like to have as a behaviour, is to have only the result of "TestMeasure" on the first row (fund) and I'd like to continue to see the individual values (NAVTodaybis) per row.

Or to have a third row with the difference/comparison only ?

Any ideas ?

thanks a lot !

[Baskar]

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]

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]

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]

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]

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