Averages of Averages in DAX for Power BI

[Anonymous]

Hi All,

I'm stuck on trying to obtain averages of averages.  I have the Call count but can't seem to get the Average of that Call Count per line on the Table Visualization.  

example - 

Customer       Call Counts              Call Average Percent

Customer A    Call Count of 20         ** this is where it just keeps showing 20 and not the average.  Tried AverageX.

Customer B    Call Count of 1

Customer C    Call Count of 2 

                        Total = 23 

Also, need to take the Target / 12 months then take that value and multiply by the DateDiff (in Months) from Min date and Max date.  My table will only show the value once so I'm having issues obtaining the multiplied value because the top line shows the value but the rest of the rows do not.  

Average Monthly Target / Average Calls

Thank you!!

[CheenuSing]

Hi @Anonymous

Check this out

https://community.powerbi.com/t5/Desktop/Calculate-Average-of-Averages/td-p/19720

Cheers

CheenuSing

[Anonymous]

Can DAX do something like this - 

Taking the First date and Last Date of the time frame entered, I change that into a DateDiff by Month.

=MONTH(MAXIFS(A4:A16,B4:B16,F4)-(MINIFS(A4:A16,B4:B16,F4)))

=10/12

=H5*I5

 =SUMIFS(C4:C24,B4:B24,F4,D4:D24,G4)

=[@[Calls for Period]]/[@[Target by Months]] 

 Group by 

Customer; The Rank of that Customer (1-4);  Months (Jan-Feb)

Fields(Measures and Calc columns) -

Monthly Goal / 12 months; then multipy that by Calls that Month 

*  Monthly Goal = Ranking[Target] / 12  Example 10 for the year, so 10 /12

*  Month Goal_Rank = DATEDIFF([ScheduledMin],[ScheduledMax],MONTH)  -example - say the value is 10 - I need it to show 10 across every month.  As soon as I put Month in I lose the number I need (need to ignore the Month context in the matrix, however show by month) If the Date difference overall is 10 months, I want to use 10 months across the board

*  Monthly Goal * Month Goal_Rank

Wondering if it would be best to create a DAX table that contains these fields rather than joining etc.

The issue I'm having is trying to put just the Month and Overall Value into a matrix but I use the Month Goal.  

Example attached.

Thank you!

[v-shex-msft · Accepted solution]

Hi  @Anonymous,

Based on my understanding, sumif and minif seems like get the total/min value based on specify conditions, so you can try to combination use sum/min function and filter function or direct use sumx/minx function.

For example:

SUMIF = CALCULATE(SUM([ColumnName]),FILTER(ALL(Table),[ConditionColumn]="Conditions"))

SUMIF2 =SUMX(FILTER(ALL(Table),[ConditionColumn]="Conditions"),[ColumnName])

MINIF = CALCULATE(MIN([ColumnName]),FILTER(ALL(Table),[ConditionColumn]="Conditions"))
MINIF2 =MINX(FILTER(ALL(Table),[ConditionColumn]="Conditions"),[ColumnName])

Regards,

Xiaoxin Sheng