Table1 - Table2

[ShankarS]

I have 2 tables of identical structures with one of them having Global Sales (Table-GS) and another having a specific Category's Sales (Table-C1). They have a specific key that'll be common & identifying the Date & Stores along with around 10 Sales KPIs (Sales, Cost, Margin, Units, etc). The report need to enable analysis of this category sales performance vs rest of the categories and thus will require (Table-GS) - (Table-C1).

• What is best approach to get this done? 
• Does DAX have a way to get this computed in a crisp manner, at the table level, OR will it require complex / long DAX statement mentioning each of those 10 KPIs where the difference is likely required?

Appreciate sharing your thoughts and insights to handle this elegantly.

[dedelman_clng]

Hi @ShankarS - can you provide sample data or ideally a copy of your PBIX with sensitive data removed? You can use dropbox, OneDrive, GoogleDrive etc to share a link to your file.  Also, please include expected results based on the data you provide.

David

[ShankarS]

Appreciate your reply, David.

The 2 tables being referred are of same structure - below is a stipped down version, to highlight the structure, with Date & Store as the Dimensions and have corresponding Transaction Volume, Sales Volume, Discount Volume, Margin, Units Sold, etc.

(Have updated above content as simple text, as pasting or adding an image was throwing an error).

The Table-1 holds the Chain level information while the Table-2 holds a particular Product category level, say for Grocery - with the report comparing the Grocery performance Vs Non-Grocery.

Currently we have each of those Non-Grocery KPIs as individual measures & having them computed as a difference between respective Chain & Grocery KPIs. Ex. Non-Grocery_Sales as Chain_Sales - Grocery_Sales and so on, before using these Grocery & Non-Grocery KPIs within the report visuals.

I was looking for Best Practices or possible ways of doing a Table level subtraction, instead of the manual approach of setting up individual measures and repetitive but similar formula - especially since these are common scenarios that'll be faced by many and we happen to have almost 10 KPIs and faced unnecessary issue, due to manual error in 1 of the measure definition.

Hope this provides clarity on my question. Please let me know.

[dedelman_clng]

Hi @ShankarS - sorry but I'll still having trouble understanding how your report is set up, what your model looks like, expected results, etc. In Power BI and DAX there is no concept that I know of for "Table-level subtraction", which as you describe sounds like subtracting one number matrix from another, element by element.

Generally, best practice is that you set up a measure for each calculation that you want to do, unless you have your model set in a way that one or multiple slicers can take advantage of a single calculation but look at it with different filters. Most reports I have done involve many measures, and they are often chained together as the calculations get more detailed.

If you can share your report, I or others here may be able to help you, but being able to investigate and troubleshoot from a narrative is going to be problematic.

David