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

Average Inventory

Hi,

 

I am trying to calculate GMROI per item and am having trouble getting the average inventory cost.

 

My data is something like this:

DateStoreItemSOHValue
30 May 2022A110
30 May 2022B120
6 June 2022A110
6 June 2022B120
13 June 2022A110
13 June 2022B120

 

If I try 

AvgSOHValue = AVERAGE(ItemSales[SOHValue])
I get 15 because it divides total SOH (90) by the number of records (6).

 

Instead I want the 90 divided by the number of dates (3), so I'm getting an average SOH overall instead of per store.

 

Can anyone please help?

 

Thanks,
Michael

1 ACCEPTED SOLUTION
ddpl
Solution Sage
Solution Sage

@mboucher_rcr 

 

Create below measure and put it in card

 

Avg = SUM(ItemSales[SOHValue]) / DISTINCTCOUNT(ItemSales[Date])
 
Hope it will work for you.

View solution in original post

1 REPLY 1
ddpl
Solution Sage
Solution Sage

@mboucher_rcr 

 

Create below measure and put it in card

 

Avg = SUM(ItemSales[SOHValue]) / DISTINCTCOUNT(ItemSales[Date])
 
Hope it will work for you.

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors