Sum Values within a criteria

[bcassilhas]

Hi Community!

I need a measure what sum the lowest price of every product (matching the ID), according to the schema below (our price and competidor price):

When i dont select a competidor, it just sums all the rows following date and store filters:

Can someone help me??  :((

[v-rongtiep-msft]

Hi @bcassilhas ,

Please refer to my steps to see if it helps you.

Create a new table includes category column , and without relationship between the 2 tables.

Then create 2 columns.

LOWCOMPETIDOR =
VAR _1 =
    MINX (
        FILTER (
            'Table',
            'Table'[date] = EARLIER ( 'Table'[date] )
                && 'Table'[CATEGORY] = EARLIER ( 'Table'[CATEGORY] )
        ),
        'Table'[PRICE COMPETIDOR]
    )
RETURN
    IF ( 'Table'[PRICE COMPETIDOR] = _1, _1, BLANK () )

lowourprice =
VAR _1 =
    MINX (
        FILTER (
            'Table',
            'Table'[date] = EARLIER ( 'Table'[date] )
                && 'Table'[CATEGORY] = EARLIER ( 'Table'[CATEGORY] )
        ),
        'Table'[OUR PREICE]
    )
RETURN
    IF ( 'Table'[OUR PREICE] = _1, _1, BLANK () )

Finally create 2 measures.

OURPRICEMEASUR =
VAR _selected =
    SELECTEDVALUE ( 'Table (2)'[category] )
VAR _sumlow =
    CALCULATE (
        SUM ( 'Table'[lowourprice] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[CATEGORY] = SELECTEDVALUE ( 'Table (2)'[category] )
        )
    )
VAR _sumall =
    CALCULATE (
        SUM ( 'Table'[OUR PREICE] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[CATEGORY] = SELECTEDVALUE ( 'Table (2)'[category] )
        )
    )
RETURN
    IF ( ISFILTERED ( 'Table (2)'[category] ), _sumall, _sumlow )

PRICECOMPETIDORMEASU =
VAR _selected =
    SELECTEDVALUE ( 'Table (2)'[category] )
VAR _sumlow =
    CALCULATE (
        SUM ( 'Table'[LOWCOMPETIDOR] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[CATEGORY] = SELECTEDVALUE ( 'Table (2)'[category] )
        )
    )
VAR _sumall =
    CALCULATE (
        SUM ( 'Table'[PRICE COMPETIDOR] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[CATEGORY] = SELECTEDVALUE ( 'Table (2)'[category] )
        )
    )
RETURN
    IF ( ISFILTERED ( 'Table (2)'[category] ), _sumall, _sumlow )

Best Regards

Community Support Team _ Polly

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

[bcassilhas]

Polly, thank you for the answer!

Maybe i wasnt too much clear, but i think i couldnt get the output i wanted. The lowest price column needs to be matching by id. Below, theres a part of the dataset with the 2 new columns. The arrows refers to Product ID, Our Price and Competidor Price, respectively. The new calculated columns aren't returning any price value for this ID (256).

I want to build a matrix like this (sum lowest prices of products of each category):

Category	Our Price	Competidor Price
Drinks	$3500	$3400
X	$1200	$1300
Y	$2000	$2200

I didn't understood too much the category slicer that you put to change the values. I have Date, Store and Competidor Slicers on my report, and the matrix above needs to filter according to these criterias.

Thank you! 🙂

[v-rongtiep-msft · Accepted solution]

Hi @bcassilhas ,

If I have misunderstood your meaning before, please provide your pbix file without privacy information and more details with your desired output.

How to Get Your Question Answered Quickly 

Best Regards

Community Support Team _ Polly

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