AVERAGE BY CATEGORY

[Anonymous]

@v-xjiin-msft  @Vera_33 @Zubair_Muhammad 

I need help with DAX to create a measure

I have a table with Vessel name and Tonnage Capacity

Vessel Name	Tonnage Capacity
Vessel ABC	0
Vessel XYZ	25202.11
Vessel YTJ	29881.42
Vessel IUK	31085.14
Vessel LMN	27712.07

I want to create a measure called Tonnage Capacity of Vessel which will throw the corresponding Tonnage Capacity for every vessel when I want to create power pivots etc

I have tried using the following measures

Average Capacity per Vessel = CALCULATE ('Tonnage Data Table'[TONNAGE CAP], ALLEXCEPT ( 'Trips Data Table', 'Trips Data Table'[Vessel] ) ) - This measure is throwing the average of the entire column when I am using in Power pivot instead of giving me the tonnage capacity against each vessel

AVERAGE 2 = VAR _VesselName = MAX('Tonnage Data Table'[Vessel Name] ) RETURN

CALCULATE(

AVERAGE('Tonnage Data Table'[Tonnage Capacity]),

'Tonnage Data Table'[Vessel Name] = _VesselName,

REMOVEFILTERS('Tonnage Data Table')

) This measure returns 0 instead of the corresponding tonnage capacity

Average =

CALCULATE (

AVERAGE ( 'Tonnage Data Table'[Tonnage Capacity] ),

FILTER ( ALLSELECTED ( 'Tonnage Data Table' ), 'Tonnage Data Table'[Vessel Name] = MAX ( 'Tonnage Data Table'[Vessel Name] ) )

)

Please help

[Anonymous]

Hi @Anonymous ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or if you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Best Regards,
Eyelyn Qin

[Vera_33 · Accepted solution]

Hi @Anonymous 

It was solved, @Anonymous sent me files. It was an Excel connected to PBI dataset, using fact table column to retrieve data from dim table.

[Anonymous]

Hi @Anonymous ,

Quick summarization:

Or create a measure:

average measure = CALCULATE(AVERAGE('Tonnage Data Table'[TONNAGE CAP]),ALLEXCEPT('Tonnage Data Table','Tonnage Data Table'[Tonnage Vessel Name]))

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Vera_33]

Hi @Anonymous 

I am not very sure what you want to achieve...your table only has 2 columns, and you want to select a Vessel Name in a slicer? to return an avg on Capacity of the same table? I saw there is another table Trips Data Table, what is in there? what is the relationship between these two tables?

[Anonymous]

I need to create  a measure which will give me the tonnage capacity of every vessel when I drop the same in power pivot. However I am unable to create such measure. When I am using the measures I have created in Power Pivot, I get the average of the entite column instead of corresponding tonnage capacity against each vessel name on dropping the measure in values section

[Vera_33]

Hi @Anonymous 

I can't get what is the relationship between your tables, and how your results came out like the screenshot. Can you provide a sample file? Will pm you my email address.

[Anonymous]

Hello @Vera_33 

I am using Power Pivot in excel for my final analysis

Yes, I have 2 set of excel workbooks, Trips data table(Contains columns like Tonnage , Revenue) and Tonnage data table Contains columns like Vessel name and Tonnage Capacity

I need to create  a measure which will give me the tonnage capacity of every vessel when I drop the same in power pivot. However I am unable to create such measure. 

[Anonymous]

Hello

[StefanoGrimaldi]

if its for a simple visual a simple average function with nothing more will work, when you put in a visual table the average value dax + the category or vessel name the in build filter of the visual will separate that average dax by the category you added, 

asl tyou can try something like this: 

dax name = 

var X = "put there the column name of the category column"

var y = calculate(average(value field), category field column = X)

return Y

[Anonymous]

I am unable to create the measure as you have directed. Please refer the screenshot below

Just want to give move background. I have a Tonnage Data Table with 2 columns 

Tonnage Capacity  - Tonnage Data Table'[Tonnage Capacity] and Tonnage Vessel Name  - Tonnage Data Table'[Tonnage Vessel Name]