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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
JLdH
New Member

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

 

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

 

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
Baskar
Resident Rockstar
Resident Rockstar

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 .

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

Baskar
Resident Rockstar
Resident Rockstar

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

 

 

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

 

Capture10.JPG

 

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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.