Calculate subtotals average of columns

[Salva71]

Hello -

Bewing new to Power Pivot and not familiar with DAX, I would appreciate some help with this task:

I have a dataset of production costs by product number, work order number, and facility. I need to display the average cost of all work orders at a given facility and for a given product on a table. Is there a DAX formula that can help me do that? Below is a screenshot of my pivot, in which the subtotal and Grand Total show sums instead of averages (my intended result).  

Thank you for your help!

Salva

[DataNinja777 · Accepted solution]

Hi @Salva71 ,

You can calculate the average cost of all work orders at a given facility and for a given product using the AVERAGEX function in DAX. This function iterates over a table and evaluates an expression for each row, returning the average. The formula to use is:

Average Cost = AVERAGEX(
    VALUES('YourTable'[Work Order Number]), 
    CALCULATE(SUM('YourTable'[Cost/Case]))
)

In this formula, VALUES('YourTable'[Work Order Number]) retrieves the unique work orders in the current filter context, ensuring that the calculation considers each work order separately. The CALCULATE(SUM('YourTable'[Cost/Case])) ensures that for each work order, the total cost is summed up correctly before being averaged. AVERAGEX then computes the final average across all work orders.

To apply this in your Power Pivot table, create a new measure using the formula above and use it in your pivot table instead of the current sum aggregation. If needed, ensure that the aggregation type in the Values area is set to Average. This will display the correct average cost rather than summing up the costs, which is the issue in your current pivot table setup. Let me know if you need further clarification!

Best regards,

[Salva71]

It worked perfectly! Thank you, DataNinja777 🙏

May I ask this community where to find good training material on DAX?