Forum Discussion
Get total sum Help!
- 4 years ago
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 - 4 years ago
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.
- 4 years ago
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
- Ashish_Mathur4 years agoSuper User
Hi,
Share the link from where i can download your PBI file.
- SilviaM4 years agoFrequent Visitor
I can't share the pbxi 😞 i don't have any site to do it. Sorry about that!
- PaulDBrown4 years agoCommunity Champion
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-xiaotang4 years agoCommunity Support
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.