Hi everyone,
Need a bit of help with the following problem I am trying to address. Attached, I have included the sample data that I am working on. The data itself is not complex. I am basically calculation the pay progression amongst employees for the last 5 years. As you can imagine, this is a percentage. To find the average, I am using this dax:
Av Var 4Yr =
CALCULATE(
AVERAGEX(
FILTER(Data, Data[Var 4Yr] <> 0),
Data[Var 4Yr]
)
)
This is giving me the correct average by Department or Job type. However, the total is wrong. I have followed Sam’s video where he shows the total sales but had to change it for average growth. This is my Dax so far:
Testing Totals =
VAR AveragebyLine =
CALCULATE(
AVERAGEX(Data, [Av Var 4Yr]
)
)
VAR AveragebyGrade =
SUMMARIZE(
Data,
Data[Grade],
“Average”,
CALCULATE(
AVERAGEX(Data, [Av Var 4Yr]
)
)
)
Something that might help the forum is what you expecting the correct solution you are looking for and what you got with calcuations that you stated with your dax calculation.
Hi @Keith,
Thank you for getting back on this. Below would be what I would expect the result to be. Whilst the row results are correct, the total is not.
Measures behave differently for individual rows and totals because the filter context at the row level contains only data relevant to that row (e.g., each grade), while the total row removes these filters to aggregate across all rows.
You could use something like this:
Testing Totals Revised =
VAR __AveragebyGrade =
SUMMARIZE(
Data
, Data[Grade]
, "Average", AVERAGEX(Data, Data[Var 4Yr])
)
VAR Result =
IF(
HASONEVALUE(Data[Grade])
, AVERAGEX(Data, Data[Var 4Yr])
, AVERAGEX(__AveragebyGrade, [Average])
)
RETURN
Result
This ensures that the average Var 4Yr is pre-aggregated by grade in the virtual table and the conditional logic ensures that if only one grade is selected, the average is calculated directly from the raw data. For the total row, it computes the value by averaging the pre-aggregated averages from the summarized table.
This avoids the double aggregation in your original measure and calculates each grade’s average only once.
Thank you so much for looking into this. Unfortunately, the dax you supplied gives me completely different answer. Neither the row results nor the total is what I was expecting. Is it possible that the zero value rows are counted here when my calculation was trying to now count them?
Hi @Jawed - In your dataset, there is no data available for 10 - Exec Dir, how are you expecting 0% for them in the expected results. Also, it seems you are also considering them when trying to get 64 and 24 value respectively.
Based on PBIX, your calculation is already giving correct results if we don’t consider 0% values. If you want to consider blank values as 0, then can modify below part of code.
VAR AveragebyGrade =
SUMMARIZE(
Data,
Data[Grade],
"Average",
if(isblank(CALCULATE(
AVERAGEX(Data, [Av Var 4Yr]
))),0,CALCULATE(
AVERAGEX(Data, [Av Var 4Yr]
)
)
))
Both Dax is not giving me the right numbers (The one from @HufferD and the updated one you suggested.
See the PBIX file attached. My Dax shows the correct row levels but not the correct total. The one you guys suggested is seem to show the correct row and total values.
Hi @Jawed - Is below your expected result. Do let me know how you are expecting “0” for 10 - Exec Dir when it is not available in sample data or are you maintaining a separate Dimension table that is not provided.
I looked at the file you shared. The table visual in the sample is reproduced below:
A row exists for Grades 20–70, but not for “Grade 10 - Exec Dir”, but it sounds you are expecting a row for that grade. Given the only available values for Data[Grade] are:
you won’t have a row for “Grade 10 - Exec Dir” unless a separate dimension table describing grades exists and that table has a relationship with Data[Grade].
If we stick to just that table, can you be more specific about exactly what totals are incorrect and exactly what values you expect instead?
Thank you for reaching out and apologies for the confusion. The list that I have shown above in response to @ankit is what I am looking for. However, the list I showed is coming from excel. In the PBI, I do not want the rows with zero to be either shown, or counted as part of the average.
Hi @Jawed - I think you are getting confused or we are understanding incorrectly. Can you share what is your expected result from Power BI as that is still not clear.
You are saying you don’t want to consider rows with zero to be either shown, or counted as part of the average.
So, after removing 0 row what results are you expecting, Do share expected result in a table format.
