cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JLdH Frequent Visitor
Frequent Visitor

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

 

Maybe with a screenshot Smiley Happy :

 

Capture1.JPG

 

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 Smiley Wink )

 

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 !

5 REPLIES 5
Super User
Super User

Re: Subtract values from same category but from different subcategories, show total on category leve

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

Re: Subtract values from same category but from different subcategories, show total on category leve

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):

 Capture2.JPG

 

Those are fund values (name=index):

Capture3.JPG

 

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

 

Capture4.JPG

 

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

 

Capture5.JPG

Super User
Super User

Re: Subtract values from same category but from different subcategories, show total on category leve

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

Re: Subtract values from same category but from different subcategories, show total on category leve

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

 

Capture10.JPG

 

Community Support Team
Community Support Team

Re: Subtract values from same category but from different subcategories, show total on category leve

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.

3.PNG

 

Thanks,
Yuliana Gu

 

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.