cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SilviaM
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.

 

SilviaM_0-1645736805027.png

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:
 
SilviaM_1-1645737213908.png

 

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

 

Can somebody help me please!!!

Thanks in advance!

3 ACCEPTED SOLUTIONS
Greg_Deckler
Super User
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

View solution in original post

Hi @SilviaM 

will it help? the sample file attached below.

vxiaotang_0-1646215406007.png

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.

View solution in original post

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

duprow values.jpg

 

 





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!
Paul on Linkedin.






View solution in original post

8 REPLIES 8
Ashish_Mathur
Super User
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
https://www.linkedin.com/in/excelenthusiasts/

Hi Ashish, 

I could not attached a MS Excel file.

WBSAreasum of RevRev Real by WBSYear_Month% Revenue
1-0000004511-3Servers3967181983592021_01 
1-0000004511-3Virtualization1983591983592021_017%
1-0000004511-3Servers394676.24197338.122021_02 
1-0000004511-3Virtualization197338.12197338.122021_027%
1-0000004511-3Servers395697.12197848.562021_03 
1-0000004511-3Virtualization197848.56197848.562021_037%
1-0000004511-3Servers421391.24210695.622021_04 
1-0000004511-3Virtualization210695.62210695.622021_048%
1-0000004511-3Servers421391.24210695.622021_05 
1-0000004511-3Virtualization210695.62210695.622021_058%
1-0000004511-3Servers421391.24210695.622021_06 
1-0000004511-3Virtualization210695.62210695.622021_068%
1-0000004511-3Servers421391.24210695.622021_07 
1-0000004511-3Virtualization210695.62210695.622021_078%
1-0000004511-3Servers421391.24210695.622021_08 
1-0000004511-3Virtualization210695.62210695.622021_088%
1-0000004511-3Servers421391.24210695.622021_09 
1-0000004511-3Virtualization210695.62210695.622021_098%
1-0000004511-3Servers421391.24210695.622021_10 
1-0000004511-3Virtualization210695.62210695.622021_108%
1-0000004511-3Servers417828.98208914.492021_11 
1-0000004511-3Virtualization208914.49208914.492021_118%
1-0000004511-3Servers424953.5212476.752021_12 
1-0000004511-3Virtualization212476.75212476.752021_128%
1-0000004511-3Servers421391.24210695.622022_01 
1-0000004511-3Virtualization210695.62210695.622022_018%
   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

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

duprow values.jpg

 

 





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!
Paul on Linkedin.






Hi @SilviaM 

will it help? the sample file attached below.

vxiaotang_0-1646215406007.png

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.

Hi,

Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

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

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

SilviaM_0-1645747431521.pngSilviaM_1-1645747442589.png

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,

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

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.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

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

June 20 episode 7 with aka link 768x460.jpg

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