sumif or equivalent

[Cyrilbrd]

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

Code	Value1	Value2
A	12	9
B	10	8
A	13	7
C	9	5
A	11	7

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

[Cyrilbrd]

@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.

[v-jianboli-msft · Accepted solution]

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]

Allow me to rephrase it.

Code	Value1	Value2
A	12	9
B	10	8
A	13	7
C	9	5
A	11	7

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.

[Padycosmos]

Hope this helps

[Cyrilbrd]

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

But i still get an incorrect result. 

[Padycosmos]

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

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

[Cyrilbrd]

@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]

Hi,

May I ask, what is

code_cleaned

?

[Cyrilbrd]

@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?