Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Karolina411
Resolver II
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)

 

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

 

Karolina411_0-1674604101561.png

 

4 ACCEPTED SOLUTIONS
transform99
Resolver I
Resolver I

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

View solution in original post

THanks---I tried that and go this: 

Karolina411_0-1674606713547.png

 

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

View solution in original post

Karolina411
Resolver II
Resolver II

Some are jus not adding correctly: 

Karolina411_1-1674607194394.png

 

View solution in original post

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

View solution in original post

9 REPLIES 9
v-jianboli-msft
Community Support
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?

vjianbolimsft_1-1674699549153.png

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.

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

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

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.

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

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
Resolver II
Resolver II

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

Karolina411
Resolver II
Resolver II

Some are jus not adding correctly: 

Karolina411_1-1674607194394.png

 

transform99
Resolver I
Resolver I

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

THanks---I tried that and go this: 

Karolina411_0-1674606713547.png

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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