cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ggp Frequent Visitor
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
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
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
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,D4Smiley Very Happy24,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!

Screenshot.jpg

 

Community Support Team
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: | |