Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!!
Solved! Go to 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
Hi @Anonymous
Check this out
https://community.powerbi.com/t5/Desktop/Calculate-Average-of-Averages/td-p/19720
Cheers
CheenuSing
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!
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |