Forum Discussion
Subtotals incorrect on Table visual
I have created a measure for my table visual (Min Stock Requirement) but the subtotals didn't work. I have tried creating a second measure using HASONEFILTER incorporating the original measure, but because the table has more than one filter I can't get it to work correctly. I have tried nesting the HASONEFILTER, that doesn't work either. Is there anyone who can help - I can't believe I am the only person in the world that needs this! My table is the one below. I need the subtotal on Min Stock Req 2 column corrected (it should be around 2700), the measure I have done so far is also below.
Hi Anonymous ,
We can simplify your metric to make sure it is calculated correctly at different levels of aggregation. You can use the ISINSCOPE function to check if the current context is at the level of detail you want.Min Stock Req 2 = IF( ISINSCOPE('Inventory_Detail_Total'[Device Name]) && ISINSCOPE('Inventory_Detail_Total'[Part Simple]) && ISINSCOPE('Inventory_Detail_Total'[Supplier]), [Min Stock Requirement], SUMX( SUMMARIZE( 'Inventory_Detail_Total', 'Inventory_Detail_Total'[Device Name], 'Inventory_Detail_Total'[Part Simple], 'Inventory_Detail_Total'[Supplier] ), [Min Stock Requirement] ) )
This measure uses ISINSCOPE to check if the current context is at the Device Name, Part Simple, and Supplier level of detail. If it is, it returns the Min Stock Requirement, and if it is not, it aggregates the data at the desired level and then sums the Min Stock Requirement.If the above changes can't be resolved, you amount can provide full example data and MEASUREMENTS so we can help you faster. Please hide sensitive information in advance.
Best regards,
Albert HeIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly
3 Replies
Hi Anonymous ,
We can simplify your metric to make sure it is calculated correctly at different levels of aggregation. You can use the ISINSCOPE function to check if the current context is at the level of detail you want.Min Stock Req 2 = IF( ISINSCOPE('Inventory_Detail_Total'[Device Name]) && ISINSCOPE('Inventory_Detail_Total'[Part Simple]) && ISINSCOPE('Inventory_Detail_Total'[Supplier]), [Min Stock Requirement], SUMX( SUMMARIZE( 'Inventory_Detail_Total', 'Inventory_Detail_Total'[Device Name], 'Inventory_Detail_Total'[Part Simple], 'Inventory_Detail_Total'[Supplier] ), [Min Stock Requirement] ) )
This measure uses ISINSCOPE to check if the current context is at the Device Name, Part Simple, and Supplier level of detail. If it is, it returns the Min Stock Requirement, and if it is not, it aggregates the data at the desired level and then sums the Min Stock Requirement.If the above changes can't be resolved, you amount can provide full example data and MEASUREMENTS so we can help you faster. Please hide sensitive information in advance.
Best regards,
Albert HeIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi,
Share the download link of the PBI file and show the problem there clearly.
