Count the number of item ID where price has changed

[Adritch]

Hello i have a long list of many different item [ID] that each have many observations of the price. Every time the price changes, the table updates with a new [Created Date]. The old price will have active = 0. Each ID exists in several regions.

For each [ID] i need to calculated two things: 

- Has there been a  price change for this ID?

- Has the price changed in all regions?

Created Date	ID	Region	Price	Active
17/04/2018	Potato	EU	0.2	0
15/06/2018	Potato	EU	0.15	0
03/07/2019	Potato	EU	0.1	1
17/04/2018	Carrot	US	0.3	0
15/05/2018	Carrot	US	0.25	0
03/07/2019	Carrot	US	0.2	1
17/04/2018	Potato	US	0.4	1

[Anonymous · Accepted solution]

Hi @Adritch ,

You can try to use the following measure formulas if they suitable for your requirement:

Changes in Current Region =
VAR summary =
    SUMMARIZE ( T2, [ID], [Region], "DC", COUNTROWS ( VALUES ( T2[Price] ) ) )
RETURN
    IF ( COUNTROWS ( FILTER ( summary, [DC] > 1 ) ) > 0, "Y", "N" )

Changes between All Regions =
VAR summary =
    SUMMARIZE (
        FILTER ( T2, [Active] = 1 ),
        [ID],
        "DC", COUNTROWS ( VALUES ( T2[Price] ) )
    )
RETURN
    IF ( COUNTROWS ( FILTER ( summary, [DC] > 1 ) ) > 0, "Y", "N" )

Regards,

Xiaoxin Sheng

[Tara_]

Hi @Adritch 

If I understood your questions correctly, you want to find how many IDs and Regions had a change in price regardless of how many times they changed. I replicated your data and added an ID that didn't have a change in price for testing purposes and wrote the below measure

PriceChanged = CALCULATE(DISTINCTCOUNT(table1[ID]),

FILTER(table1,table1[Active]=0))

Using the measure I got the below results

Hope this helps

[Adritch]

Hi Tara, thank you for you reply.

I tried your solution, but it does not quite match what i have worked to myself and i am not quite sure why.

My best solution so far is taking each ID, count how many times a price appears for this id and making a distinct count of how many regions this ID appears in.

If there are more counts of prices than there are regions, it must mean that the price has changed?

For example:

ID	Number of Prices	Number of Regions	PriceChanged
Banana	252	16	TRUE
Orange	16	16	FALSE

This was done by creating a new table with the formula:

SUMMARIZE('pricess' , 'prices'[ID], "PriceChanged" , IF(COUNTROWS('prices')>distinctcount('prices'[region]),TRUE(),FALSE()))

Using this, i can then count how many ID that have PriceChanged = True.

Does this make sence?

[Tara_]

I didn't really get what you're trying to say. The example you provided says that Orange has 16 number of prices (meaning it changed 16 times) but in the Price Changed column it says False. How does that happen?

Also in the solution I provided, the 1 represents when a price is changed. IDs that have not changed were not displyed in the visual (in the example I provided: Onion) and hence the total refers to the total number of IDs that had price changed. This is still my understanding of your request, if there is more info you can provide, maybe it'll become clearer. 

I just saw your edit. Yes after creating that table and then use the same logic in the measure I provided earlier to count how many True values you have. 

[Adritch]

In the example i provided, the price of an orange appears 16 times, because there are 16 different regions. It thus appears only once in each region, and therefore has not changed

[Tara_]

Ok got it and responded in my previous post.