Forum Discussion
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 =
Where % Revenue = (Rev Real by WBS / 5401004) -->total of sum Column Rev Real by WBS)
Can somebody help me please!!!
Thanks in advance!
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
8 Replies
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 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])))returnif(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,
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.
