Comparing Prices in Different Markets for Same Vendors

[Anonymous]

Hello there,

I want to create a true/ false column for items that have different prices in diffrent markets for the same vendor for the same item.

In the below table, Big Al's Supply is $2.00 across all 3 markets so the result should be false. Fruits R US is $1.15 in 2 markets but $2.50 in one, so it would be true. Ringo's Grocery is all over the place with their prices for an apple so they also get a true. There are also many items (banana, pear, etc) that also have price discrepancies.

MARKET	VENDOR	ITEM NAME	PRICE	Discrepancy
OK	BIG AL'S SUPPLY	APPLE	2.00	0
OK	FRUITS R US	APPLE	1.15	1
OK	RINGO'S GROCERY	APPLE	1.00	1
TX	BIG AL'S SUPPLY	APPLE	2.00	0
TX	FRUITS R US	APPLE	1.15	1
TX	RINGO'S GROCERY	APPLE	1.50	1
LA	BIG AL'S SUPPLY	APPLE	2.00	0
LA	FRUITS R US	APPLE	2.50	1
LA	RINGO'S GROCERY	APPLE	1.75	1

I know I need to make a new column with a formula but I have no idea the logic to make Power BI compare the columns based on these criteria. Any advise is appreciated!

Edit: I want to also note that the end goal of this is to create a dashboard that notes shows all of the items across all markets that have different prices. I just thought it would be easier if I had a column that I could easily filter if there were a price discrepancy!

Thanks,

KL

[v-rzhou-msft · Accepted solution]

Hi @Anonymous 

I build a sample to have a test.

My Table contains different VENDORs and different ITEM NAMEs.

Add a calculated column to achieve your goal.

Discrepancy = 
VAR _Discount =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[PRICE] ),
        FILTER (
            'Table',
            'Table'[VENDOR] = EARLIER ( 'Table'[VENDOR] )
                && 'Table'[ITEM NAME] = EARLIER ( 'Table'[ITEM NAME] )
        )
    )
RETURN
    IF ( _Discount > 1, "True", "FALSE" )

Result is as below.

You can download the pbix file from this link: Comparing Prices in Different Markets for Same Vendors

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Anonymous]

Thanks @v-rzhou-msft !

I knew I had to do some sort of filter but did not know where to start.

Thank you 🙂

KL