Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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 :
Solved! Go to Solution.
I have found a solution.
Here's what I did.
First I have created a calculated column as below
@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
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHello,
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
I have found a solution.
Here's what I did.
First I have created a calculated column as below
@Anonymous ,
sumx(values(projectskey),[Test])
or
sumx(values(projectskey),max(table[JTDBudgetRevenueAmt]))
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
87 | |
84 | |
66 | |
49 |
User | Count |
---|---|
127 | |
109 | |
93 | |
70 | |
67 |