Forum Discussion

GreenKnight1294's avatar
GreenKnight1294
Frequent Visitor
3 years ago
Solved

SUMIFS Equivalent to Check for Row Value

Good evening.   I'm trying to build a SUMIFS equivalent formula that checks the value of a given row and sums the values in a column that match the values of that row. I wrote the following formu...
Need Help
  • Ritaf1983's avatar
    Ritaf1983
    3 years ago

    Hi GreenKnight1294  on this table my solution works.

    I can't figure out what the problem is.

    "Fix" a cell in POWER BI is not possible because of the logic it uses

    Rather than working at the cell level, it works at the column level.

    It's a game where you release filters and apply filters in a context.

    Sumifs in the context of multiple conditions is like I showed calculate ([measure], condition A && condition B etc..)

     

    Unfortunately, I don't know how to help beyond that...

  • Ahmedx's avatar
    Ahmedx
    3 years ago

    pls try this

    Column = 
VAR t1 = [Registry]
VAR t2 = [Type]
VAR _Results = SUMX(FILTER(ALL('Table'),'Table'[Registry]=t1&&'Table'[Type]=t2),[QTY])
RETURN
_Results

Ritaf1983's avatar
Ritaf1983
Icon for Super User rankSuper User
3 years ago

Hi GreenKnight1294 
Unfortunately, I don't know how it works in power pivot.
In power bi to achieve your goal you can add a calculated column with Dax formula :

test = if([Registry]="2290"&&'Table (2)'[Type]="print",CALCULATE(sum('Table (2)'[QTY]),ALLEXCEPT('Table (2)','Table (2)'[Registry],'Table (2)'[Type])),'Table (2)'[QTY]).

I also updated a sample file .
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

GreenKnight1294's avatar
GreenKnight1294
Frequent Visitor
3 years ago

Hey, Rita,

 

Sorry I couldn't answer sooner.

 

I tried out your solution and unfortunately this isn't the answer I'm looking for. Your formula is only adding the registries that share "2290" and "print" and I want a formula that can reference one of the columns in the table to get the value "2290" rather than a fixed value.

 

If the row has the value "2290" in the registry column I want it to add all the values that have 2290, but if another row has 2291 I want it to add all the values that have 2291 as well, and so on.

 

Do you know if this is possible using DAX?

 

Thank you so much for your time.

  • Ritaf1983's avatar
    Ritaf1983
    Icon for Super User rankSuper User
    3 years ago

    Hi GreenKnight1294 .

    I am sorry , i just not sure i understan what you have vs what you need.

    Please share your table un iseful format ( not screenshot)  and desired result. I will try to help 

    • GreenKnight1294's avatar
      GreenKnight1294
      Frequent Visitor
      3 years ago

      Hey Ritaf1983 

       

      It seems I accidentally replied to the main post and not to you directly. Just wanted to let you know.

       

      Thanks.

      • Ritaf1983's avatar
        Ritaf1983
        Icon for Super User rankSuper User
        3 years ago

        Hi GreenKnight1294  on this table my solution works.

        I can't figure out what the problem is.

        "Fix" a cell in POWER BI is not possible because of the logic it uses

        Rather than working at the cell level, it works at the column level.

        It's a game where you release filters and apply filters in a context.

        Sumifs in the context of multiple conditions is like I showed calculate ([measure], condition A && condition B etc..)

         

        Unfortunately, I don't know how to help beyond that...