Look for rows with the same value in one column and one of two items in another column

[Anonymous]

Hi All,

I’m very new to Power BI, so please forgive the obvious questions.

My manager would like me to use information from one of our databases to make a model of a proposed program. Some changes need to be made to the data from the database to fit parameters of the proposed program.

One of the things I need to find and correct is a double counting of two items in the original information. In the example below, Green Apples have been double counted so the number of green apples show up in both the Green Apples row and the Apples row for each store. This is only a problem when the store reports both Green Apples and Apples units.

I ultimately want to search for stores that report both Green Apples and Apples and then, for those stores only, have a Corrected Units figure for Apples that is equal to the original Apples Units minus the original Green Apples Units. All other rows would have a Corrected Units figure that is the same number as in the original Units column.

Right now I'm just trying to figure out how to search for rows with the same value in the Store column and either "Green Apples" or "Apples" in the Item column. I’ve played with Lookupvalue but I can’t figure out how to get the formula to look for the two different text strings in the same column.

Does anybody have any tips?

Thanks in advance for your help!

STORE        || ITEM               || UNITS               || Wanted Results: Corrected Units

Uptown      || Apples            ||     7                   ||        7

Uptown      || Strawberries   ||     9                   ||        9

Uptown      || Grapes            ||     4                   ||        4

Downtown || Apples             ||     5                   ||        2  (The units of Green Apples are subtracted from the units of Apples (5-3=2).

Downtown || Green Apples  ||     3                   ||        3

Downtown || Pears               ||     10                 ||        10

Suburbs     || Green Apples  ||     1                   ||        1

Suburbs     || Cantaloupes    ||     6                   ||        6

Suburbs     || Pineapples       ||     2                   ||        2

[Ashish_Mathur]

Hi,

Will there only be a Green Apples there or can there by Red apples, Yellow bananas as well?

[Anonymous]

Hi Ashish,

Right here we’re only talking about the Green Apples and Apples.

In the information our database is giving to me there won’t be any Red Apples but I will be gathering the Yellow Bananas and Green Bananas units into a single line under Bananas. Also, those Grapes are going to get divided into Green Grapes and Red Grapes, both with and without seeds.

And that is all just to get ready to do the work on the actual model.

It’s going to be great fun and you’ll probably be seeing more of my questions.

Thanks!

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

Please create a calculated column with this formula:

Wanted Units =
IF (
    'Fruit Table'[ITEM] = "Apples",
    'Fruit Table'[UNITS]
        - CALCULATE (
            SUM ( 'Fruit Table'[UNITS] ),
            FILTER (
                ALLEXCEPT ( 'Fruit Table', 'Fruit Table'[STORE] ),
                'Fruit Table'[ITEM] = "Green Apples"
            )
        )
        + 0,
    'Fruit Table'[UNITS]
)

Best regards,

Yuliana Gu

[Anonymous]

Hi Yuliana,

Awesome! That works!!

Can you tell me what purpose the “+ 0,” in the third to last line serves? I removed it and the formula still works.

Thanks so much for your help!