Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
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.
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
Proud to be a Super User!
Paul on Linkedin.
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
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
Proud to be a Super User!
Paul on Linkedin.
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.
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!
@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
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=
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |