How to get the total sum of each sub-group within a group

[Anonymous]

Hello, here's an extract of my table

projectskey	WBS1	fiscalPeriod	JTDBudgetRevenueAmt	Test
1322248	12M-00017-06	202002	7825	7825
1322248	12M-00017-06	202003	7825	7825
1322248	12M-00017-06	202004	7825	7825
1322248	12M-00017-06	202005	7825	7825
1322248	12M-00017-06	202006	7825	7825
1322248	12M-00017-06	202007	7825	7825
1322250	12M-00017-06	202002	4800	4800
1322250	12M-00017-06	202003	4800	4800
1322250	12M-00017-06	202004	4800	4800
1322250	12M-00017-06	202005	4800	4800
1322250	12M-00017-06	202006	4800	4800
1322250	12M-00017-06	202007	4800	4800
1322400	12M-00017-06	202002	13470	13470
1322400	12M-00017-06	202003	13470	13470
1322400	12M-00017-06	202004	13470	13470
1322400	12M-00017-06	202005	13470	13470
1322400	12M-00017-06	202006	13470	13470
1322400	12M-00017-06	202007	13470	13470
1354603	12M-00017-06	202002	103700	103700
1354603	12M-00017-06	202003	103700	103700
1354603	12M-00017-06	202004	103700	103700
1354603	12M-00017-06	202005	103700	103700
1354603	12M-00017-06	202006	103700	103700
1354603	12M-00017-06	202007	103700	103700
1354722	12M-00017-06	202002	47100	47100
1354722	12M-00017-06	202003	47100	47100
1354722	12M-00017-06	202004	47100	47100
1354722	12M-00017-06	202005	47100	47100
1354722	12M-00017-06	202006	47100	47100
1354722	12M-00017-06	202007	47100	47100

The value of the calculated colum is :

Test =

VAR MaxJTDBudgetRevAmtPProject=CALCULATE(MAX('Project''s Info'[JTDBudgetRevenueAmt]),ALLEXCEPT('Project''s Info','Project''s Info'[WBS1],'Project''s Info'[projectskey]))

RETURN MaxJTDBudgetRevAmtPProject

Those amount are repeated due to the presence of the fiscal period.

How can I get the total amount per WBS1 using a calculated column .  In this example (7825+4800 +13470+103700+47100)=
176 895

Regards

[edhans]

@Anonymous can you consider using a measure for this? You are going to run into issues with using CALCULATE() in calculated columns as they will start to create unintended circular references, in addition to the other issues calculated columns present. This measure will return what you need I believe:

Measure = 
VAR varWBS1 = MAX('Table'[WBS1])
VAR Result = 
    SUMX(
        FILTER(
            ALL('Table'),
            'Table'[WBS1] = varWBS1
        ),
        'Table'[JTDBudgetRevenueAmt]
    )
RETURN
    Result

In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables
Creating a Dynamic Date Table in Power Query

[Anonymous]

Hello, 

None of the above mentioned solution seems to work.  Here's more details about the table.

WBS1= 12M-00017-06, which has 5 sub-projects (1322248, 1322250, 1322400, 1354603, 13254722)  .  So I looking for a way to get a calcualted columns to sum (7825 4800 13470 103700 47100) = 1765895, the number I am looking for.  We need to sum for each project (wbs1) the various max amount associated to each sub-project.

Is there a way to do that.

Regards

[Anonymous · Accepted solution]

I have found a solution.

Here's what I did.

First I have created a calculated column  as below

MaxJTDBudgetRevAmtPProject =

VAR MaxJTDBudgetRevAmtPProject=CALCULATE(MAX('Project''s Info'[JTDBudgetRevenueAmt]),ALLEXCEPT('Project''s Info','Project''s Info'[WBS1],'Project''s Info'[projectskey]))

RETURN MaxJTDBudgetRevAmtPProject

Then I have created another calculated column as below

MaxJTDBudgetRevAmtPWBS1 =

calculate(SUMX (

SUMMARIZE ( 'Project''s Info', 'Project''s Info'[projectskey], 'Project''s Info'[WBS1], "MaxAmount", MAX ( 'Project''s Info'[MaxJTDBudgetRevAmtPProject]) ),

[MaxAmount]

),ALLEXCEPT('Project''s Info','Project''s Info'[WBS1]))

[amitchandak]

@Anonymous ,

sumx(values(projectskey),[Test])
or
sumx(values(projectskey),max(table[JTDBudgetRevenueAmt]))