Multiple criteria and data sources

[Chxlsea_]

Hi, I'm currently trying to create a table which compares the standard cost to the actual cost. So far i have the standard cost by category/sub assembly however, I'm having trouble with the actual costs. Currently the actual costs are pulling through as a whole sum. For instance,                           

Sub assembly	Standard Cost	Actual Cost
1	£700	£1400
2	£300	£1400

When the table brings through the actuals it needs to be filtered through several other sets of data. The table needs to pick up the same sub assembly category, then filter by the same financial year, and then also filter by product type. It then also needs to sum together these peices of data as there can be multiple for each sub assembly. Not sure if anyone can advise how i go about doing this? All the data is there im just not sure how to tell PowerBI to do this. 

[Ritaf1983 · Accepted solution]

Hi @Chxlsea_ 

It sounds like your actuals data is coming through at a higher level of aggregation than your standard cost data, which is likely causing the issue with incorrect totals.

If you're pulling in an already aggregated value (e.g., total actual cost per sub assembly), and Power BI tries to summarize it again — you’ll get duplicated sums.

To avoid this, you can consider one of the following techniques:

1. Use MIN or MAX instead of SUM in your measure — if there’s only one row per context, these will return the correct value without aggregating it again.

2. Add a row index within each sub-assembly category using Power Query, and in your measure only use the first row (e.g., filter by index = 1). This method is more robust when there are multiple rows per category that must be collapsed to one.

Here's a useful article on how to generate row numbers in Power Query:
https://radacad.com/create-row-number-for-each-group-in-power-bi-using-power-query

That said, to provide accurate help, we’d need to see your actual data model — not just a 2-row example. Please prepare a small sample PBIX file that mirrors your model structure, with only the relevant columns and relationships, and upload it to any public cloud storage (OneDrive, Google Drive, etc.) so we can assist further.

If this post helps, then please consider Accepting it as the solution and giving Kudos to help the other members find it more quickly

[v-ssriganesh]

Hi @Chxlsea_,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-ssriganesh]

Hi @Chxlsea_,

May I ask if you have resolved this issue? If so, please mark it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-ssriganesh · Accepted solution]

Hello @Chxlsea_,
Thank you for reaching out to the Microsoft Fabric Forum Community.

I’ve reproduced your scenario in Power BI Desktop based on the requirement you shared comparing Standard Cost to Actual Cost by SubAssembly, while applying multiple filters such as Financial Year and Product Type to the Actual Cost calculation.

I was able to get the expected output where the Actual Cost is correctly summed and filtered by:

• Matching SubAssembly,
• Selected Financial Year,
• Selected Product Type.

For your reference, I’m attaching a .pbix file that includes:

• Sample data tables for StandardCost and ActualCost.
• A FilterSelection table to simulate filter inputs.
• A DAX measure Actual Cost Filtered using CALCULATE and FILTER() to apply the logic.

Please find the attached PBIX file to explore the implementation.

If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

[Chxlsea_]

Hi, this is really helpful however, does not solve my problem. The filterselection sheet used would mean in my case only one 'product type' or in my case project definition would be able to be shown. i need the table to change and be able to show all the different project difinitions one at a time depending what the user selects on the filter. 

[v-ssriganesh]

Hi @Chxlsea_,
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please accept it as a solution and give it a 'Kudos' so other community members with similar problems can find a solution faster.
Thank you.

[Ritaf1983 · Accepted solution]

Hi @Chxlsea_ 

It sounds like your actuals data is coming through at a higher level of aggregation than your standard cost data, which is likely causing the issue with incorrect totals.

If you're pulling in an already aggregated value (e.g., total actual cost per sub assembly), and Power BI tries to summarize it again — you’ll get duplicated sums.

To avoid this, you can consider one of the following techniques:

1. Use MIN or MAX instead of SUM in your measure — if there’s only one row per context, these will return the correct value without aggregating it again.

2. Add a row index within each sub-assembly category using Power Query, and in your measure only use the first row (e.g., filter by index = 1). This method is more robust when there are multiple rows per category that must be collapsed to one.

Here's a useful article on how to generate row numbers in Power Query:
https://radacad.com/create-row-number-for-each-group-in-power-bi-using-power-query

That said, to provide accurate help, we’d need to see your actual data model — not just a 2-row example. Please prepare a small sample PBIX file that mirrors your model structure, with only the relevant columns and relationships, and upload it to any public cloud storage (OneDrive, Google Drive, etc.) so we can assist further.

If this post helps, then please consider Accepting it as the solution and giving Kudos to help the other members find it more quickly