Forum Discussion

levi_banks's avatar
levi_banks
New Member
2 years ago
Solved

Average Unit Cost per Date

Hello, 

 

I am working with a transactional database that consists of daily reports. Each report consists of the individual line items from a number of "open orders" from our order processing system. Each line item has a unit cost associated with it, and I am trying to calculate the average unit cost per report date. However, no matter what I try, the output of the visualizations shows only the overall average for the whole column of unit costs, and it shows it for each date (i.e. 8.10.23 -> $16.75, 8/11/23 -> 16.75, etc.). 

 

I have tried the following things so far: 

- I have tried plotting Unit Cost vs. Report Date directly as a line chart, and I get a horizontal line showing the overall average of the whole column for each date.

- I have tried creating the following measure, which gives me the same result as the previous thing I tried: 

 

 

Average Unit Cost per Report Date = AVERAGEX(ALL('Fact - Open Orders'[Report Date]), AVERAGE('Dim - Order Line Item'[Unit Cost]))

 

 

 

I have attached photos of my data model as well as the fields of the two tables involved in this calculation. Could someone please help me to understand where I am going wrong here?

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi levi_banks 

    Please try the follwing measure.

    Average Unit Cost per Report Date =
AVERAGEX (
    VALUES ( 'Fact - Open Orders'[Report Date] ),
    CALCULATE ( AVERAGE ( 'Dim - Order Line Item'[Unit Cost] ) )
)

    Best Regards!

    Yolo Zhu

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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi levi_banks 

    Please try the follwing measure.

    Average Unit Cost per Report Date =
AVERAGEX (
    VALUES ( 'Fact - Open Orders'[Report Date] ),
    CALCULATE ( AVERAGE ( 'Dim - Order Line Item'[Unit Cost] ) )
)

    Best Regards!

    Yolo Zhu

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

    • levi_banks's avatar
      levi_banks
      New Member
      2 years ago

      Yay, this worked! Thank you so much, I was tearing my hair out over this one. 

    • levi_banks's avatar
      levi_banks
      New Member
      2 years ago

      Wait, actually, your measure didn't quite work as written. However, I accidentally typed it in as the following measure and it worked. 

      Average Unit Cost per Report Date =
AVERAGEX (
    VALUES ( 'Fact - Open Orders' ),
    CALCULATE ( AVERAGE ( 'Dim - Order Line Item'[Unit Cost] ) )
)