DAX, calculate percentages based on date or selected results

[ccarpent]

Hi

I am struggling a little with what should be a simple percentage calculation.  I am doing this with DAX, but I am using Excel PowerPivot so I hope you don't mind me posting this question here.  I seem to be getting the calcualtion correct based on each row, but the comparison aganist the total when based selevct justa few members not working.

My data is basically repeated every few days, and then I use a pivot/matrix to make a selction based on 'DateChecked'

DataSet

So when I select a date, the matric works out the percentages correctly, see column 'TotalComparison%' this is the entire list for 01/10/2022.

Result when all selected

However when I just focus say two people for example, the 'TotalComparison%' calculation is now based on currennt selection and not the orginal total mentioend above.

result when I select two for example

Any help approciated, and sampe data posted here:

DateChecked	Name	TotalNoTasks	TotalFinished	TotalToBeDone	TotalNotFinished
01/10/2022	JOHN	12	3	0	9
01/10/2022	PAUL	15	0	0	15
01/10/2022	ARTHUR	24	3	15	6
01/10/2022	CHARLES	10	7	0	3
01/10/2022	SARA	9	3	0	6
01/10/2022	FIONA	27	12	3	12
15/10/2022	JOHN	18	3	0	15
15/10/2022	PAUL	18	12	3	3
15/10/2022	ARTHUR	15	6	6	3
15/10/2022	CHARLES	3	0	0	3
15/10/2022	SARA	3	0	3	0
15/10/2022	FIONA	3	0	3	0
01/11/2022	JOHN	6	3	3	0
01/11/2022	PAUL	3	0	3	0
01/11/2022	ARTHUR	36	13	14	9
01/11/2022	CHARLES	12	6	0	6
01/11/2022	SARA	9	3	3	3
01/11/2022	FIONA	3	3	0	0
17/11/2022	JOHN	3	0	0	3
17/11/2022	PAUL	18	1	14	3
17/11/2022	ARTHUR	12	0	9	3
17/11/2022	CHARLES	3	3	0	0
17/11/2022	SARA	39	15	3	21
17/11/2022	FIONA	3	0	0	3

Many thanks

ChrisC

[v-yadongf-msft · Accepted solution]

Hi @ccarpent ,

This is my test table:

I create following columns:

TotalFinshed% = DIVIDE('Table'[TotalFinished],'Table'[TotalNoTasks])

TotalToBeDone% = DIVIDE('Table'[TotalToBeDone],'Table'[TotalNoTasks])

TotalNotFinished% = DIVIDE('Table'[TotalNotFinished],'Table'[TotalNoTasks])

TotalComparison% = 
var sum_notasks = CALCULATE(SUM('Table'[TotalNoTasks]),ALLEXCEPT('Table','Table'[DateChecked]))
return
DIVIDE('Table'[TotalNoTasks],sum_notasks)

I think this is the result you want:

When you select two people:

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[daXtreme]

What's the formula you use for TotalComparison%? I understand you've created a measure to calculate this and not using the automatically created measures? Because if you do, the output you get is precisely what you should expect. You cannot achieve what you want by using automatically generated formulas. You have to code it yourself.