Help creating an average calculation
Hi All,
Currently, I am working on a project to show the daily inventory quantity at the item level and the movement of it compared to the average quantity.
I receive a daily report of the inventory and the quantity, and in the Power BI, I have the excel report append.
I have created a filter to show the inventory quantity from todays date, but have problems when I look at the average.
I am showing this as a bar graph, with a hierachy set up (Product line > Item code as the x axis and quantity as the y axis)
Ex.
Hiearchy :
Chocolate Bars = Product Line
Kit Kat, Mars Bar = Item Code
Report Example:
| Date | Item Code | Qty |
| 1/1/2020 | Kit Kat | 5 |
| 1/1/2020 | Mars Bar | 2 |
| 1/1/2020 | Coffee Crisp | 3 |
| 1/2/2020 | Kit Kat | 6 |
| 1/2/2020 | Mars Bar | 2 |
| 1/2/2020 | Coffee Crisp | 4 |
| 1/3/2020 | Kit Kat | 4 |
| 1/3/2020 | Mars Bar | 3 |
| 1/3/2020 | Coffee Crisp | 3 |
If today is 1/3/2020, then the quantity of Kit Kat is 4, Mars Bar 3, Coffee Crisp 3. The Product line quantity is 10.
The average that I want is Product line = 11, . But in Power BI, it shows as 3.55 quantity.
How can I get it to show Total Average Quantity is 11, then drill down to show averages across Item level (Kit Kat = 4.3, Mars Bar 2.7, Coffee Crip = 3.7) ?
You may try the measure below.
Measure = AVERAGEX ( VALUES ( 'Table'[Date] ), CALCULATE ( SUM ( 'Table'[Qty] ) ) )
