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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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

Hello, here's an extract of my table

 

projectskeyWBS1fiscalPeriodJTDBudgetRevenueAmtTest
132224812M-00017-0620200278257825
132224812M-00017-0620200378257825
132224812M-00017-0620200478257825
132224812M-00017-0620200578257825
132224812M-00017-0620200678257825
132224812M-00017-0620200778257825
132225012M-00017-0620200248004800
132225012M-00017-0620200348004800
132225012M-00017-0620200448004800
132225012M-00017-0620200548004800
132225012M-00017-0620200648004800
132225012M-00017-0620200748004800
132240012M-00017-062020021347013470
132240012M-00017-062020031347013470
132240012M-00017-062020041347013470
132240012M-00017-062020051347013470
132240012M-00017-062020061347013470
132240012M-00017-062020071347013470
135460312M-00017-06202002103700103700
135460312M-00017-06202003103700103700
135460312M-00017-06202004103700103700
135460312M-00017-06202005103700103700
135460312M-00017-06202006103700103700
135460312M-00017-06202007103700103700
135472212M-00017-062020024710047100
135472212M-00017-062020034710047100
135472212M-00017-062020044710047100
135472212M-00017-062020054710047100
135472212M-00017-062020064710047100
135472212M-00017-062020074710047100

 

 

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
 
1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

4 REPLIES 4
edhans
Super User
Super User

@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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

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
Not applicable

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
Super User
Super User

@Anonymous ,


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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors