Adding two % columns to get an average of the two in DAX

[Karolina411]

Hello Power Users:

I derived the % from this method: (How can I add them together then get an average of the two???)

MINUTES DETAILS

%minutesexpected = [Totaltime]/[TotalAVGMIN]

(MINUTES DEFINED)

[Totaltime] = [SumHUBTask] * [AvgMinPerTask]

SumHUBTask = sum('Time_Matrix'[pValue])

MinPerTask = divide([SumWeekMinutes],[SumActivity],0)

SumWeekMinutes = sum([C_Time])

SumActivity = sum([pValue])

TotalAVGMIN = [AvgMtdProdMin] * [EmployeeCNT]

AvgMtdProdMin = distinctcount(DateDim[YearMonth]) *7200

EmployeeCNT = DISTINCTCOUNTNOBLANK('Time_Matrix'[HUB_EmployeeName])

AvgMtdProdMin = distinctcount(DateDim[YearMonth]) *6500

ENCOUNTERS

%ageEncounters_Monthly_1 = calculate(divide([SumMonthlyEncounters_1], 400,0))

SumMonthlyEncounters_1 = TOTALMTD([#Encounters_1],DateDim[DateValue])

#Encounters_1 = COUNT(Time_Matrix_PHC[EncounterKey])

[transform99 · Accepted solution]

If I'm understanding it correctly, you're wanting the average of [%Minutes Met - Monthly] and [%Encounters Expected]?

If so; is there something stopping the following measure:

Average = DIVIDE ( ([%Minutes Met - Monthly] + [%Encounters Expected]), 2)

Logically PowerBI should be able to handle the data types behind the scenes as decimal points

(e.g.

[0.7] + [0.4] = 1.1

1.1 / 2 = 0.55

convert to % = 55%).

[Karolina411 · Accepted solution]

THanks---I tried that and go this: 

I think it must be relating to the TIME FACTOR---

[Karolina411 · Accepted solution]

Some are jus not adding correctly: 

[v-jianboli-msft]

Hi @Karolina411 ,

Are %ageEncounters_Monthly_1 and %minutesexpected the %Encounters Expected and %Minutes Met-Monthly in the figure respectively?

Why are there two different AvgMtdProdMin?

Sorry for that the information you have provided is not making the problem clear to me. Can you please share more details to help us clarify your scenario?

Please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

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

[Karolina411]

I reworked it by multiplying the % by .50 then adding them together: 

AVG_1 = [50%Encounters]+[50%Minutes] Thank you for your reply

[transform99]

can you share the formulas for both [%Minutes Met - Monthly] and [%Encounters Expected] and confirm my understanding of what you're aiming to achieve based on manually calculated data:

- you're aiming to achieve an average ratio of 2 percentiles. In example 1, it is (76.33% + 52.26%)/2 = 64.295%

If the above is a correct understanding, the breakdown of all measures that feed into [%minutes Met - Monthly] and [%Encounters Expected] should help too.

It may also be worth noting you have included 2 AvgMtdProdMin calculations for different productive minutes, and if they are being referenced it may be causing issues.

[Karolina411 · Accepted solution]

Yes I am trying to achieve an average ration of 2 percentiles-that is correct.

I do this by the following

%Minutes Met = 

MINUTES DETAILS

%minutesexpected = [Totaltime]/[TotalAVGMIN]

(MINUTES DEFINED)

[Totaltime] = [SumHUBTask] * [AvgMinPerTask]

SumHUBTask = sum('Time_Matrix'[pValue])

MinPerTask = divide([SumWeekMinutes],[SumActivity],0)

TotalAVGMIN = [AvgMtdProdMin] * [EmployeeCNT]

AvgMtdProdMin = distinctcount(DateDim[YearMonth]) *7200

EmployeeCNT = DISTINCTCOUNTNOBLANK('Time_Matrix'[HUB_EmployeeName])

%Encounters Expected

%ageEncounters_Monthly_1 = calculate(divide([SumMonthlyEncounters_1], 400,0))

SumMonthlyEncounters_1 = TOTALMTD([#Encounters_1],DateDim[DateValue])

#Encounters_1 = COUNT(Time_Matrix_PHC[EncounterKey])

[Karolina411]

It must be the way it is adding relating to the something date related---I just cannot work it out!  It seems so simple---

[Karolina411]

but some are ---like the first---

[Karolina411 · Accepted solution]

Some are jus not adding correctly: 

[transform99 · Accepted solution]

If I'm understanding it correctly, you're wanting the average of [%Minutes Met - Monthly] and [%Encounters Expected]?

If so; is there something stopping the following measure:

Average = DIVIDE ( ([%Minutes Met - Monthly] + [%Encounters Expected]), 2)

Logically PowerBI should be able to handle the data types behind the scenes as decimal points

(e.g.

[0.7] + [0.4] = 1.1

1.1 / 2 = 0.55

convert to % = 55%).

[Karolina411 · Accepted solution]

THanks---I tried that and go this: 

I think it must be relating to the TIME FACTOR---