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 filter.
Example

CodeValue1Value2
A129
B108
A137
C95
A117


I would like a measure that would give:
Measure for value 1: A = 65.5% (12+13+11) divided by 55
Measure for value 2: A = 63.9% (9+7+7) divided by 36

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.

10 Replies

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Community Support
    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?

       

  • Cyrilbrd's avatar
    Cyrilbrd
    Helper IV
    3 years ago
    Measure 2 = divide( calculate(sum(Main[Value1]),Main[code_cleaned] ="88-NL-A"),sum(Main[Value1]))

    But i still get an incorrect result. 

    • Padycosmos's avatar
      Padycosmos
      Solution Sage
      3 years ago

      You need to use the formula in the screenshot exactly as shown. The formula you have typed is incorrect.

  • Cyrilbrd's avatar
    Cyrilbrd
    Helper IV
    3 years ago

    Allow me to rephrase it.

    CodeValue1Value2
    A129
    B108
    A137
    C95
    A117

     

    With the code being ten of thousand or rows.
    Filters at page level as to show data only for selected codes is existing.
    Slicer to show locations, or statuses or department are also existing and are applied via interactions.
    CUrrently both solutions are showing incorrect info.
    The solution of Padycosmos is the closest but works only if all filters and slicers are off.
    Code A ratio for value 1 could be total COG (Cost Of Goods) in a specific location the it could be for all locations.
    Thus allowing comparison of KPI.
    If I was to "Show value as Percent of grand total", then it would give me a percenatge that would not work unless ALL codes were displayed.
    Showing only selected codes would render "Show value as Percent of grand total" useless.

  • Cyrilbrd's avatar
    Cyrilbrd
    Helper IV
    3 years ago

    v-jianboli-msft Thanks for the input, it works as requested.
    Interrestingly I would have never thought of REMOVEFILTER.

    The solution works with both slicers and filters.
    Thanks.