Get total sum Help!

[SilviaM]

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!!!

Thanks in advance!

[Greg_Deckler · Accepted solution]

@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

[v-xiaotang · Accepted solution]

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.

[Ashish_Mathur]

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. 

[SilviaM]

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

[PaulDBrown · Accepted solution]

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

[v-xiaotang · Accepted solution]

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.

[Ashish_Mathur]

Hi,

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

[SilviaM]

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

[Greg_Deckler · Accepted solution]

@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

[SilviaM]

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,