In Power BI, how can I calculate the result of a Measure with respect to either of two columns?

Question

If I have an Orders table like this:

Order ID	Number of units sold	Price paid (USD)	Shipping country	Billing country
1000000	2	120	United States
1000001	1	10500	Canada	United States
1000002	4	400	France	Canada
1000003	12	6250	United States	Canada
1000004	5	2150	France	France

And I have some DAX Measures, e.g.:

Value = SUM('Orders'[Price paid (USD)])
Volume = SUM('Orders'[Number of units sold])
Average Unit Price =
SUMX(
'Orders',
DIVIDE('Orders'[Price paid (USD)], 'Orders'[Number of units sold])
)

Then clearly I could add e.g. 'Orders'[Shipping country] to the rows of a PBI Matrix and calculate the Measures with respect to having Shipping country in the filter context. And likewise for Billing country.

But what I want is to calculate my Measures with respect to country, by which I mean the country name, irrespective of which column it came from. What I want is:

Country name	Value	Volume
Canada	17150	17
France	2550	9
United States	16870	15

So that:

• IDs that feature two countries, e.g. 1000001 and 1000002, contribute to both of those countries' rows. So these IDs are double counted.
• IDs that only have one country, or that list the same country twice, i.e. 1000000 and 1000004, only contribute once to their country. E.g. 1000004 doesn't get double-counted against France.

Many thanks

Answer 1

you can try this in the PQ.

1. create a column

= Table.AddColumn(#"Changed Type", "Custom", each if [Billing country] =[Shipping country] then null else [Billing country])

2. remove the billing column

3. select the first three columns and unpivot other columns

then you can get the result directly in the table visual

Answer 2

Something like this should work:

Value =
VAR country = SELECTEDVALUE ( Orders[Shipping country], BLANK() )
VAR sumPrice = 
    SUMX (
        ALL ( Orders ),
        IF ( OR ( Orders[Billing country] = country, Orders[Shipping country] = country ),
            Orders[Price paid (USD)], 0 ) )
RETURN sumPrice

The country variable is the selected country in the matrix visual. I am assuming that all possible countries are in the [Shipping country] column. So put this column on rows in your matrix visual.

In the SUMX expression, all the prices are added where the [Shipping country] or the [Billing country] equals the selected country in the visual.

The measure for Volume works the same way. Just substitute Orders[Price paid (USD)] for 'Orders'[Number of units sold] in the IF statement.