cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver II

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

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)

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])

4 ACCEPTED SOLUTIONS
Anonymous
Not applicable

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%).

Resolver II

THanks---I tried that and go this:

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

Resolver II

Some are jus not adding correctly:

Resolver II

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)

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])

9 REPLIES 9
Community Support

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?

Refer to:

How to provide sample data in the Power BI Forum

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.

Resolver II

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

Anonymous
Not applicable

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.

Resolver II

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)

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])

Resolver II

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

Resolver II

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

Resolver II

Some are jus not adding correctly:

Anonymous
Not applicable

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%).

Resolver II

THanks---I tried that and go this:

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors