Forum Discussion

Cyrilbrd's avatar
Cyrilbrd
Helper IV
3 years ago
Solved

sumif or equivalent

I am trying to get the percentage of a value in a column based on one condition. In excel I would use something along SUMIF or SUMIFS to get the value I want and would divide it by the total without...
Need Help
  • v-jianboli-msft's avatar
    v-jianboli-msft
    3 years ago

    Hi Cyrilbrd ,

     

    Here is my example:

    Please try:

    Measure for value 1 = 
var _a = SUM('Table'[Value1])
var _b = CALCULATE(SUM('Table'[Value1]),REMOVEFILTERS('Table'[Code]))
return DIVIDE(_a,_b)

Measure for value 2 = 
var _a = SUM('Table'[Value2])
var _b = CALCULATE(SUM('Table'[Value2]),REMOVEFILTERS('Table'[Code]))
return DIVIDE(_a,_b)

    Output:

     

     

    Best Regards,

    Jianbo Li

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

  • Cyrilbrd's avatar
    Cyrilbrd
    Helper IV
    3 years ago

    Jihwan_Kim Thanks for the proposal.
    I am getting a : The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column.
    I used this:

    Measure 2 = divide( calculate(sum(Main[Value1]),code_cleaned ="88-NL-A"),sum(Main[Value1]))

    Did I omit somthing?

     

    • Jihwan_Kim's avatar
      Jihwan_Kim
      Super User
      3 years ago

      Hi,

      May I ask, what is

      code_cleaned

      ?

       

       

      • Cyrilbrd's avatar
        Cyrilbrd
        Helper IV
        3 years ago

        Jihwan_Kim 

        Context:

        The data comes from a server.
        The data may need "cleaning" such as to ensure that the code is corrected.
        Code_cleaned is the code minus unwanted.
        So far the measure returns a 100% for the code of interest and percentages in excess to 100% for all other codes.
        Padycosmos 
        I only noticed your proposal this morning, and it works as displayed.
        The new issue is I have several slicers and Filters.
        Example, location, status, department are filters that I require.
        What would be the best approach to ensure that a dashboard with specifc filters would display the appropriate result?