cancel
Showing results for
Search instead for
Did you mean:
Highlighted
ggp Frequent Visitor

## Averages of Averages in DAX for Power BI

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!!

1 ACCEPTED SOLUTION

Accepted Solutions Community Support Team

## Re: Averages of Averages in DAX for Power BI

Hi  @ggp,

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |
3 REPLIES 3 Super User

## Re: Averages of Averages in DAX for Power BI

Hi @ggp

Check this out

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

Cheers

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
ggp Frequent Visitor

## Re: Averages of Averages in DAX for Power BI

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 24,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!  Community Support Team

## Re: Averages of Averages in DAX for Power BI

Hi  @ggp,

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |