Forum Discussion
Calculating Percentages for line items with data selected by slicer
I'm hoping the community can help me out here. Searchese have proved fruitless so far.
The business has requested that we show total hours by project and also show the percentage of hours each project represents compared to the whole. However, when a slicer is applied, the total number of hours used for the denominator doesn't seem to change. Here is an example.
Here is the data structure.
What we are trying to figure out is how to get the denominator in this equation to be dynamic as slicers are applied so that the data that shows on the screen in the first screenshot displays the correct percentage.
Any assistance that can be provided is greatly appreciated!
Patrick
Hi pAAmckelvey,
Based on my test, you can refer to below steps:
1.I have entered some sample data to test for your problem in below picture.
2.Create a new measure to calculate the percentage.
percentage = DIVIDE(SUM(Sheet2[Hours]),CALCULATE(SUM(Sheet2[Hours]),ALLSELECTED(Sheet2[Work Description])))
3.Create a Table visual and add the [Work Description] and the [Hours] field. Create a Slicer visual to add the [percentage] field. And now you can see the result.
You can also download the PBIX file to have a view.
Regards,
Daniel He
2 Replies
v-danhe-msftMicrosoft Employee
Hi pAAmckelvey,
Based on my test, you can refer to below steps:
1.I have entered some sample data to test for your problem in below picture.
2.Create a new measure to calculate the percentage.
percentage = DIVIDE(SUM(Sheet2[Hours]),CALCULATE(SUM(Sheet2[Hours]),ALLSELECTED(Sheet2[Work Description])))
3.Create a Table visual and add the [Work Description] and the [Hours] field. Create a Slicer visual to add the [percentage] field. And now you can see the result.
You can also download the PBIX file to have a view.
Regards,
Daniel He
Thank you so much v-danhe-msft! This worked like a charm!
