Forum Discussion

Jayfemi's avatar
Jayfemi
Icon for Helper I rankHelper I
9 years ago
Solved

SUMIF Equivalent in DAX

Hi, 

I am inthe process of moving reports from excel onto power Bi and i am having some problems finding the DAX alternative so some common formulas i use.

 

For example i am usiing this sum ifs =SUMIFS(Allocated,Month,F$8,Workflow,"Reactive on DRS")  

Need Help
Show and Tell
Tutorial Requests
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi Jayfemi,

    For example i am usiing this sum ifs =SUMIFS(Allocated,Month,F$8,Workflow,"Reactive on DRS")  

    Based on my test, the formula below should work in your scenario.

    Sum Of Allocated = CALCULATE(SUM(Data[Allocated]),Data[Workflow]="Reactive on DRS")

    Here is the sample pbix file for your reference. :smileyhappy:

     

    Regards

8 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi Jayfemi,

    For example i am usiing this sum ifs =SUMIFS(Allocated,Month,F$8,Workflow,"Reactive on DRS")  

    Based on my test, the formula below should work in your scenario.

    Sum Of Allocated = CALCULATE(SUM(Data[Allocated]),Data[Workflow]="Reactive on DRS")

    Here is the sample pbix file for your reference. :smileyhappy:

     

    Regards

  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    9 years ago

    Hey,

     

    basically you can use this pattern

    SUMX('your table'
      ,if('yourtable'[columnyousearchin] = "yoursearchvalue" 
        ,calculate(SUM('yourtable'[column you want to sum]))
        ,BLANK()
    )

     

    Hope this gets you started

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    9 years ago
    Hi Jayfemi,

    Just use the CALCULATE function something like this

    CALCULATE( SUM(Table[Allocated]), Table[Month]= Calendar[Month], Table[Workflow]="Reactive on DRS")

    Don't know you're exact model but it will be sonething like the above where the first argument is the summarization you want the rest are the filter you want to make.

    Regards,
    MFelix
  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi Jayfemi,

     

        It would be great if you have shared some sample data or the result you are trying to achieve. I still believe "calculate wrapped in if " would do what you want.

     

    Regards,

    Hemant

    • Jayfemi's avatar
      Jayfemi
      Icon for Helper I rankHelper I
      9 years ago

      Hi Anonymous 

       

      please see sample data ideally i am trying to recreate the charts on the tab Vital statistics in Bi

      • v-ljerr-msft's avatar
        v-ljerr-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        Hi Jayfemi,

        please see sample data ideally i am trying to recreate the charts on the tab Vital statistics in Bi

        Where is your sample data? I can't find it anywhere. :smileyhappy:

         

        Regards