Forum Discussion
Solved
Any help here
On the table below, starting from Account till Descritpion (I will keep appending data) and i would need to use them as filter on the visualization.
I want to calculate Cost 1% and Cost 2 % - Matching all the senarious or filter that i would select on the visual...
| Account | Fiscal | Senario | Job type | Region | Geo | Location | Quadrant | Scenario Type | Scenario Type 1 | Description | Split | H1 | H2 | Y |
| 1 | FY 18 | Budget | A | NA | US | T | P | R | R1 | DDD | Sales | 100 | 100 | 100 |
| 1 | FY 18 | Budget | A | NA | US | T | P | R | R1 | DDD | Cost 1 | 50 | 50 | 50 |
| 1 | FY 18 | Budget | A | NA | US | T | P | R | R1 | DDD | Cost 2 | 20 | 20 | 20 |
| 1 | FY 18 | Actual | A | NA | US | T | P | R | R1 | DDD | Sales | 100 | 100 | 100 |
| 1 | FY 18 | Actual | A | NA | US | T | P | R | R1 | DDD | Cost 1 | 50 | 50 | 50 |
| 1 | FY 18 | Actual | A | NA | US | T | P | R | R1 | DDD | Cost 2 | 20 | 20 | 20 |
| 2 | FY 18 | Budget | A | NA | US | T | P | R | R1 | DDD | Sales | 100 | 100 | 100 |
| 2 | FY 18 | Budget | A | NA | US | T | P | R | R1 | DDD | Cost 1 | 50 | 50 | 50 |
| 2 | FY 18 | Budget | A | NA | US | T | P | R | R1 | DDD | Cost 2 | 20 | 20 | 20 |
| 2 | FY 18 | Actual | A | NA | US | T | P | R | R1 | DDD | Sales | 100 | 100 | 100 |
| 2 | FY 18 | Actual | A | NA | US | T | P | R | R1 | DDD | Cost 1 | 50 | 50 | 50 |
| 2 | FY 18 | Actual | A | NA | US | T | P | R | R1 | DDD | Cost 2 | 20 | 20 | 20 |
Hi games1,
If I understand you correctly, the formula below should work in your scenario. :smileyhappy:
Cost 1% = DIVIDE ( CALCULATE ( SUM ( 'Table1'[Value] ), FILTER ( 'Table1', 'Table1'[Split] = "Cost 1" ) ), CALCULATE ( SUM ( 'Table1'[Value] ), FILTER ( ALL ( 'Table1' ), 'Table1'[Split] = "Cost 1" ) ) )Note: You'll need to replace 'Table' and [Value] with your real table name and column name.
Regards
1 Reply
Hi games1,
If I understand you correctly, the formula below should work in your scenario. :smileyhappy:
Cost 1% = DIVIDE ( CALCULATE ( SUM ( 'Table1'[Value] ), FILTER ( 'Table1', 'Table1'[Split] = "Cost 1" ) ), CALCULATE ( SUM ( 'Table1'[Value] ), FILTER ( ALL ( 'Table1' ), 'Table1'[Split] = "Cost 1" ) ) )Note: You'll need to replace 'Table' and [Value] with your real table name and column name.
Regards