Averaging a columns values with no filter and displaying the result staticly in rows

[Anonymous]

I have a column with percantage-values. I want to average all unique values from this column and display the static average result at every row! However; When I use different Dax codes that should do this - I get an "average" that alligns with the "gennemsnitCpr" value. The idea is simply to average a column filter free and display the same static value in all the new column's rows.

My query is: 

calculate(AVERAGE('Table'[GennemsnitICpr]),all('Table'[gennemsnitICpr]))

kunde	total	cpr	cvr	gennemsnitICpr	Average
NRGi	200	254	202	89.50%	89.50%
NRGi	200	254	020	89.50%	89.50%
NRGi	200	254	344	89.50%	89.50%
HEF	500	254	234	22.50%	22.50%
SydFyn	600	254	234	90.55%	90.55%

[BA_Pete]

Hi @Anonymous ,

My first guess is that your [GennemsnitlCpr] field is not a numerical data type.

In Power Query, select this field and change the data type to decimal. Apply to the model, then see if your column works as expected.

Pete

[Anonymous]

Hey @BA_Pete 

Nope - Didnt help. 

I found out how to get rid of "NaN". I simply changed my DAX code to: 

CALCULATE(AVERAGE('Table'[gennemsnitICpr),All('Table'[gennemsnitICpr]))

However, this output an average that alligns with "gennemsnitICpr" in each row - Which is definitly not what I want. I want a static average to be displayed in a column on all rows

[BA_Pete]

Hi @Anonymous ,

Are you able to provide a sample of your data in table format please?

When you reply, you can use this button to add data in table format:

 This makes it much quicker and easier for people to pick up a sample of your data for testing. This, in turn, means your question will be answered quicker.

Pete

[Anonymous]

Hey @BA_Pete 

I changed it to the best of my ability 😄

[BA_Pete]

No problem, that's perfect.

Make sure that your source data looks like this in Power Query before applying to the model:

Then use the following DAX formula in a new column:

Average = 
CALCULATE(
    AVERAGE(aTable[gennemsnitICpr]),
    ALL(aTable)
)

This gives me the following output (after changing the format to Percentage in the data model side, not Power Query side):

 Pete

[Anonymous]

Hey @BA_Pete 

Hey, Pete. 

How exactly do I express it as you have. Cuz I cant seem to make it appear like your formatting in your table?

Note; (gennemsnitICpr) is actually a division formula. So its value is based on the division of "cpr" / "total". I dont know if this makes a difference in the average calculation ?

[BA_Pete]

@Anonymous 

It looks like you're trying to change it in the data model rather than in Power Query.

Hit this button to open Power Query, where you can right-click on the column header and go to Change Type to change the DATA TYPE to decimal.

Then, still in Power Query, go to File>Close & Apply. This will push this change back into the data model.

Back in the model, you can then select that column again and change the FORMAT to Percentage.

Formats and Data Types are not the same thing.

Pete

[Anonymous]

Hi @BA_Pete 

Ah okay, I see!

Now there is another issue. Im essentially working on data thats coming from my company's live database. The column "gennemsnitICpr" is a column I have created in the data model overview as a division of two database-columns. If I go to Power Query I wont find "gennemsnitICpr". 

Does this mean I can only do this to columns coming from a datasource?

[BA_Pete · Accepted solution]

No, it should be fine if you've already created a calculated column.

Here I have created the [gennes...] column in the data model the same as you have, then applied the formula that is giving the correct output:

Not sure why this isn't working for you.

Pete