cancel
Showing results for
Did you mean:
Regular Visitor

## Get total sum Help!

Hi, I need your help on this issue. I have information in a table named ALL DATA of Revenue about WBS, area, and month.

Due to a WBS can have more than 1 area, revenue data is the same in the column, when I try to get the sum of those values, data is duplicated by month and WBS because it sums the Revenue by area 1 and area 2, and it's incorrect because the Revenue is just WBS' (example in column sum of Rev in the image that it's value column in the ALL DATA table)

So in order to get the correct number of the WBS' Revenue i'm using the next formula: Rev Real by WBS =

sumx(values('ALL DATA'[Year_Month]),sumx(values('ALL DATA'[WBS]),max('ALL DATA'[Value])))

Now the issue is that i need to get the total of sum data got in the Rev Real by WBS in order to get the % corresponded by month based on the Revenue and get something like below:

Where % Revenue = (Rev Real by WBS / 5401004) -->total of sum Column Rev Real by WBS)

Can somebody help me please!!!

3 ACCEPTED SOLUTIONS
Super User

@SilviaM This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition
Community Support

Hi @SilviaM

will it help? the sample file attached below.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

Super User

Does this work?

``````New Rev by WBS =
SUMX (
'Table',
CALCULATE (
IF (
MAX ( 'Table'[Area] ) = "Servers",
DIVIDE ( SUM ( 'Table'[sum of Rev] ), 2 ),
SUM ( 'Table'[sum of Rev] )
)
)
)
``````

and

``````% of Revenue =
VAR total =
CALCULATE (
[New Rev by WBS],
FILTER ( ALL ( 'Table' ), 'Table'[Area] <> "Servers" )
)
RETURN
IF (
MAX ( 'Table'[Area] ) = "Servers",
BLANK (),
IF (
ISINSCOPE ( 'Table'[WBS] ),
DIVIDE ( [New Rev by WBS], total ),
DIVIDE ( DIVIDE ( [New Rev by WBS], 2 ), total )
)
)
``````

to get

Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!

8 REPLIES 8
Super User

Hi,

In a simple MS Excel file, please show the result that you are expecting (with formulas).  I can then translate your Excel formulas into the DAX language.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Regular Visitor

Hi Ashish,

I could not attached a MS Excel file.

 WBS Area sum of Rev Rev Real by WBS Year_Month % Revenue 1-0000004511-3 Servers 396718 198359 2021_01 1-0000004511-3 Virtualization 198359 198359 2021_01 7% 1-0000004511-3 Servers 394676.24 197338.12 2021_02 1-0000004511-3 Virtualization 197338.12 197338.12 2021_02 7% 1-0000004511-3 Servers 395697.12 197848.56 2021_03 1-0000004511-3 Virtualization 197848.56 197848.56 2021_03 7% 1-0000004511-3 Servers 421391.24 210695.62 2021_04 1-0000004511-3 Virtualization 210695.62 210695.62 2021_04 8% 1-0000004511-3 Servers 421391.24 210695.62 2021_05 1-0000004511-3 Virtualization 210695.62 210695.62 2021_05 8% 1-0000004511-3 Servers 421391.24 210695.62 2021_06 1-0000004511-3 Virtualization 210695.62 210695.62 2021_06 8% 1-0000004511-3 Servers 421391.24 210695.62 2021_07 1-0000004511-3 Virtualization 210695.62 210695.62 2021_07 8% 1-0000004511-3 Servers 421391.24 210695.62 2021_08 1-0000004511-3 Virtualization 210695.62 210695.62 2021_08 8% 1-0000004511-3 Servers 421391.24 210695.62 2021_09 1-0000004511-3 Virtualization 210695.62 210695.62 2021_09 8% 1-0000004511-3 Servers 421391.24 210695.62 2021_10 1-0000004511-3 Virtualization 210695.62 210695.62 2021_10 8% 1-0000004511-3 Servers 417828.98 208914.49 2021_11 1-0000004511-3 Virtualization 208914.49 208914.49 2021_11 8% 1-0000004511-3 Servers 424953.5 212476.75 2021_12 1-0000004511-3 Virtualization 212476.75 212476.75 2021_12 8% 1-0000004511-3 Servers 421391.24 210695.62 2022_01 1-0000004511-3 Virtualization 210695.62 210695.62 2022_01 8% 2700501.88 100%

Column D28 = 2700501.88 = SUM(D27,D25,D23,D21,D19,D17,D15,D13,D11,D9,D7,D5,D3)

Column %Revenue = D3/\$D\$28

Super User

Does this work?

``````New Rev by WBS =
SUMX (
'Table',
CALCULATE (
IF (
MAX ( 'Table'[Area] ) = "Servers",
DIVIDE ( SUM ( 'Table'[sum of Rev] ), 2 ),
SUM ( 'Table'[sum of Rev] )
)
)
)
``````

and

``````% of Revenue =
VAR total =
CALCULATE (
[New Rev by WBS],
FILTER ( ALL ( 'Table' ), 'Table'[Area] <> "Servers" )
)
RETURN
IF (
MAX ( 'Table'[Area] ) = "Servers",
BLANK (),
IF (
ISINSCOPE ( 'Table'[WBS] ),
DIVIDE ( [New Rev by WBS], total ),
DIVIDE ( DIVIDE ( [New Rev by WBS], 2 ), total )
)
)
``````

to get

Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!

Community Support

Hi @SilviaM

will it help? the sample file attached below.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

Super User

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Regular Visitor

I can't share the pbxi 😞 i don't have any site to do it. Sorry about that!

Super User

@SilviaM This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition
Regular Visitor

Hi Greg,

Thanks fo rthe quick response and help, i tried to use the formula but i got noticed that also in the column Rev Real by WBS is duplicated by month, so if i tried again use the formula now to get the sum of all max values in that column i got the same value and i need it to stay fixed in order to multiplied it by Rev Real by WBS and get the %Revenue

Rev Real by WBS=

var tableop= summarize('ALL DATA','ALL DATA'[Year_Month],"REVS",sumx(values('ALL DATA'[Year_Month]),max('ALL DATA'[Value])))
return
if(HASONEFILTER('ALL DATA'[Year_Month]),sumx(tableop,[REVS]))

TOTAL= if(HASONEFILTER('ALL DATA'[wbs]),sumx('ALL DATA',[Rev Real by WBS])) here i need also to get just 1 value not sum the duplicated. Because the total is 2,700,501.8 and it's giving me 8,101,505.64

I thinks i'm doing something wrong,

Announcements

#### Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

#### Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

#### The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors