CODEWITHSUNDEEP
exceldaxdata-analysispowerpivot
Bori
Bori

Reputation: 23

How to create Calculated Measure to find matching values between two tables in Excel Power Pivot

I have two tables (i.e. 1st is 'Sales' Table, 2nd is 'Territory' table) in my Power Pivot, and I have established relationships between the two using a 'territory' column.

The 'Sales' table contains Sales Orders data (i.e. Invoice id, date, territory, customer name, product, qty, rate, total, etc.). Sales_Table_pic

The 'Cities' table contains Cities data such as territory, Area, Country. Territory_Table_pic

QUESTION: How to write a DAX Calculated Measure in MS Excel Power Pivot to find total Distinct Count of territories that had no sale.

REASON: I want to know how many distinct territories in each month had no sale (meaning the territory(ies) which did not appear in the Sales data for any selected month) via Pivot Tables I have created in my dashboard.

Pivot_Table_format

Upvotes: 1

Views: 51

Answers (1)

Michal
Michal

Reputation: 6009

You can create a new DAX measure from Power Pivot, Measures, New Measure, for example:

=CALCULATE(
    DISTINCTCOUNT('Territory'[Territory]),
    FILTER(
        'Territory',
        NOT (
            'Territory'[Territory] IN
            CALCULATETABLE(
                VALUES('Sales'[Territory]),
                'Sales'[Date] >= DATE(2024, 1, 1),  -- Start Date (Adjust manually)
                'Sales'[Date] <= DATE(2024, 3, 31)  -- End Date (Adjust manually)
            )
        )
    )
)

Create a new a Power Pivot and add whatever fields you need:

enter image description here

Upvotes: 0

Related Questions