Compare price changes across multiple files that are received weekly

[Syndicate_Admin]

How about each one of you.
From the outset grcs to read, I am not a programmer, I am an engineer with very good vision for the logic of the logical sequence of the steps of a VBA routine, I am also a fan of many years of excel, which I handle quite well, I google solutions with macros, I copy and edit them, etc. since +1 year ago I use Power BI and I have made several models,
Well, the point is that I have a situation:
1.-I receive weekly a list of materials each with its price, each line includes name of supplier one and supplier two, each with a price which can be the same or different each week, (wk15 and wk16)
2.-I need to see which materials change in price every week,
3.-I tried to upload the data (get Data) Folder option, but I can't find whether to create a measure or add a column to do the calculation

4.-There is a cell called MAPN that I also want to compare in each file

I thought about using folder because I want to deposit the new file every week and do refresh, something does not work for me when I want to use the IF, or CALCULATE, it does not let me filter, I have searched with RELATED,
I have tried to make a bridge table by copying the values of the file, removing the repeated values and relating them one to several, but then I do not know if it is necessary to use a relationship between a file or several relationships, or which relationship is the one that works best, in fn, I do not give with a solution,

Materials, MAPN, provider 1 and 2 contain alphanumeric characters,

I really want to learn, continue to grow and in the future be able to help too,
In advance many grcs,

Greetings from Mexico

[v-xiaotang · Accepted solution]

Hi @Syndicate_Admin @Anonymous 

Thanks for reaching out to us.

The easy way to solve it is to create the measures below,

CF 21 =
VAR _Price =
    CALCULATE (
        MAX ( 'Table'[Price] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Material Code] ),
            'Table'[Source.Name] <> MIN ( 'Table'[Source.Name] )
                && 'Table'[type] = MIN ( 'Table'[type] )
        )
    )
RETURN
    IF ( MIN ( 'Table'[Price] ) <> _Price, "#9BB6F0" )

CF 31 =
VAR _MAPN =
    CALCULATE (
        MAX ( 'Table'[MAPN] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Material Code] ),
            'Table'[Source.Name] <> MIN ( 'Table'[Source.Name] )
                && 'Table'[type] = MIN ( 'Table'[type] )
        )
    )
RETURN
    IF ( MIN ( 'Table'[MAPN] ) <> _MAPN, "#FFBD00" )

forgot to say, you need to do something similar to the following for your data, add a flag column, if the same sourcename has two records with the same code, then you can link the 4 records from different weeks accordingly.

result

But if you want to highlight with different colors, although we can make a quick judgment manually, for the machine, he needs to know the count of the value that needs to be highlighted first, so you can use the following measure to calculate, but it depends on in the exact order, otherwise it will cause confusion.

In this scenario, you need to add index column, please check the sample I attached below。

result

Best Regards,

Community Support Team _Tang

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