Forum Discussion
Get total sum Help!
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/547907Hi 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.
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
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.
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
Hi,
Share the link from where i can download your PBI file.
I can't share the pbxi 😞 i don't have any site to do it. Sorry about that!
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
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.
