Sum of SubTotals

[Anonymous]

Hello DAX pros,

I'm trying to find a formula which will sum up Subtotals values. In the screen below I'm looking for X value which should be in this case sum of subtotals 86+254=340. Regular formula makes just the difference between totals which is 168

Thank you in advance for your help.

[vivran22]

Hello @Anonymous ,

Is it possible to share the sample data/pbix file you are working with? I tried to recreate the scneario, but I am not sure if I am understanding the scenario correctly:

Sample data:

Category	SubCategory	Forecast	Actual
A	123	11	19
A	123	18	18
A	458	18	14
A	556	14	10
A	556	17	19
B	853	13	10
B	268	15	15
B	268	12	14
B	865	18	20
B	853	14	18
C	301	11	18
C	212	19	19
C	211	10	11

Cheers!
Vivek

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter

[Anonymous]

Hello @vivran22 

Thank you for your reply. Your sample works as you have only positive deviations on family level. If you change your data set to have at least one negative deviation than there is a case - the totals behaves differently measured on Totals and Subtotals as sign on deviation is hidden under abolute value.

Please change your value for Forecast for Family "A", Category '123' from 11 to 30 and you'll see what happens.

Category SubCategory Forecast Actual
A    123    30    19

Total deviation will go down to 4 = Abs(205-209) measured on totals while it should sum up all deviations on Subtotal familiy level 17+5+8 = 30. This is the number I'm looking for.

Moreover my Forecast values and Actuals are measures from data set. I have sent you my pbix file on your mail contact@vivran.in.

If you solve the issue you're the real hero !

Thank you

[vivran22 · Accepted solution]

Hello @Anonymous ,

You may try this:

Abs Dev Family2 =
VAR _Table =
    SUMMARIZE (
        'demantra',
        'demantra'[SubFamily/ProdLine],
        "Diff", ABS ( [Actuals for M] - [Forecast for M-2] )
    )
VAR _Sum1 =
    SUMX ( _Table, ABS ( [Actuals for M] - [Forecast for M-2] ) )
VAR _Sum2 =
    IF (
        HASONEFILTER ( demantra[SubFamily/ProdLine] ),
         (
            IF (
                ISFILTERED ( demantra[Original_Item] ),
                BLANK (),
                ABS ( [Actuals for M] - [Forecast for M-2] )
            )
        ),
        _Sum1
    )
RETURN
    _Sum2

There could be better solutions, but it should work.

Cheers!
Vivek

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter

[Anonymous]

Thank you Vivek! It's perfect for me as it works 🙂

Now I can move forward and get some lessons about variables based on your formula.

Have a good day, cheers!

Dykov

[vivran22]

@Anonymous 

You may see the following artilce on variables:

https://www.vivran.in/post/dax-using-variables

Cheers!
Vivek

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter