Forum Discussion

bcassilhas's avatar
bcassilhas
Frequent Visitor
3 years ago
Solved

Sum Values within a criteria

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??  :((

DAX
Filter Table
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      bcassilhas
      Frequent Visitor
      3 years ago

      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):

      CategoryOur PriceCompetidor 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! 🙂

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        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.