cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ElenaJohansen Frequent Visitor
Frequent Visitor

Average in a table per item

Hi 

 

The avarage cost doesn't work - see beneath:

 

Udklip.PNG

 

The cost is calculated from quantity and line amount.

The avarage cost is calculated as (but it does not work - in the above photo the avarage cost is 343,33 but i'll think it is wrong and it should be 408):

Avarage cost =
AVERAGEX(
'Excel'
;var thisYear = RELATED('Dates'[MonthYear])
return
CALCULATE(
AVERAGE('Excel'[Cost])
;ALLEXCEPT('Excel';'Excel'[Item ID])
;'Dates'[MonthYear] = thisYear))
The difference is calculated as: cost minus avarage cost (but it does not work because of the avarage cost) 
It should be 258,67 in the first line and -388 ind the secound line, and therefore the amount in the table top left should be showing (difference coloum) the total amount in March 2019 = -129,33. 
 
Hope someone can help me quick :-) 
 
Regards,
Elena
2 REPLIES 2
Community Support Team
Community Support Team

Re: Average in a table per item

Hi ElenaJohansen,

According to your description, my understanding is that you want to calculate the average data based on the item id. In this scenario, we can use the following DAX query:

Average cost = CALCULATE(AVERAGE(Excel[Cost]),FILTER(ALLEXCEPT(Excel,Excel[Item ID]),'Dates'[MonthYear] = thisYear))

Best Regards,

Teige

ElenaJohansen Frequent Visitor
Frequent Visitor

Re: Average in a table per item

Hi @TeigeGao 

 

I cannot use the DAX query - it tells me following:

Udklip.PNG

Hope you can help me

 

Best regards

Elena Johansen

 

 

 

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 6 members 853 guests
Please welcome our newest community members: