Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Cyrilbrd
Helper IV
Helper IV

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

1 ACCEPTED SOLUTION
v-jianboli-msft
Community Support
Community Support

Hi @Cyrilbrd ,

 

Here is my example:

vjianbolimsft_0-1675045382625.png

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:

vjianbolimsft_1-1675045429330.png

vjianbolimsft_2-1675045451284.png

 

 

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.

View solution in original post

10 REPLIES 10
Cyrilbrd
Helper IV
Helper IV

@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
Community Support
Community Support

Hi @Cyrilbrd ,

 

Here is my example:

vjianbolimsft_0-1675045382625.png

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:

vjianbolimsft_1-1675045429330.png

vjianbolimsft_2-1675045451284.png

 

 

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
Helper IV
Helper IV

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.

Padycosmos
Solution Sage
Solution Sage

Hope this helps

Padycosmos_0-1674726441004.png

 

Cyrilbrd
Helper IV
Helper IV

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

But i still get an incorrect result. 

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

Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

Jihwan_Kim_0-1674723670132.png

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


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

 

Hi,

May I ask, what is

code_cleaned

?

 

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.