Forum Discussion
How should i be doing this to develop the visualization?
In your scenario, each of the columns from Account till Description can be the filter for your table. And you want to calculate the Cost1% or Cost2% based on these filters. Right?
If so, you can refer to following method:
You can use DAX expression like below to calculate the total count of table rows.
Total Account = CALCULATE(COUNTROWS('Cost Percent'),ALLSELECTED('Cost Percent'))Also use expression like this to calculate the count of each Split values.
CALCULATE(COUNTROWS('Cost Percent'),ALLSELECTED('Cost Percent'),VALUES('Cost Percent'[Split]))Then you can combine the two expression with Divide() function to calculae the Cost % like
Cost % = DIVIDE ( CALCULATE ( COUNTROWS ( 'Cost Percent' ), ALLSELECTED ( 'Cost Percent' ), VALUES ( 'Cost Percent'[Split] ) ), CALCULATE ( COUNTROWS ( 'Cost Percent' ), ALLSELECTED ( 'Cost Percent' ) ) )The result show like this:
Thanks,
Xi Jin.v-xjiin-msft Thanks a lot for your insights, this is working perfectly as needed.
Apologies, I am trying differrnet options and did not want to share the official data was the reason for having a week question.
Here is the real issue that i am facing or need help
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 |
Solution Sage
In your scenario, each of the columns from Account till Description can be the filter for your table. And you want to calculate the Cost1% or Cost2% based on these filters. Right?
If so, you can refer to following method:
You can use DAX expression like below to calculate the total count of table rows.
Total Account = CALCULATE(COUNTROWS('Cost Percent'),ALLSELECTED('Cost Percent'))Also use expression like this to calculate the count of each Split values.
CALCULATE(COUNTROWS('Cost Percent'),ALLSELECTED('Cost Percent'),VALUES('Cost Percent'[Split]))Then you can combine the two expression with Divide() function to calculae the Cost % like
Cost % =
DIVIDE (
CALCULATE (
COUNTROWS ( 'Cost Percent' ),
ALLSELECTED ( 'Cost Percent' ),
VALUES ( 'Cost Percent'[Split] )
),
CALCULATE ( COUNTROWS ( 'Cost Percent' ), ALLSELECTED ( 'Cost Percent' ) )
)The result show like this:
Thanks,
Xi Jin.
v-xjiin-msft Thanks a lot for your insights, this is working perfectly as needed.