Forum Discussion

Cyrilbrd's avatar
Cyrilbrd
Icon for Helper IV rankHelper 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
Icon for Helper IV rankHelper 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.