Forum Discussion
How should i be doing this to develop the visualization?
I am trying to do a daily sale targetted pace report, i will create the table that is best to get this out
Filter
Month
Date
Week
Employee
Manger
Ouput -
Every day of the month when selected sales till date compared vs the target and gap.... update the revised targets per day for the remaining days considering the overall target
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.
4 Replies
Your shared information is too simple for us to understand your requirement. Please kindly share us more detailed information like some sample data and your desired result.
By the way, it seems like that you want to create some kind of report which is used to compare current part of data vs the target and display the gap and update the revised target. Right?
Please refer to following sample report, see if it helps you:
Thanks,
Xi Jin.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 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.