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?