# Power BI Exchange

View Only

## Calculate the percentage of a column based on one of the values in the column

• #### 1.  Calculate the percentage of a column based on one of the values in the column

Posted 16 days ago

This is a very odd request.  It is difficult to explain in words, so let me show you what I am trying to do.  Lets say I have a table with the following values.

Category    Sales Qty    Current Year % Of Sales Compared to Helmets
Helmets      150                             150/150 = 100%
Gloves         70                                70/150 = 47%
Pants           80                                80/150 = 53%

I have tried doing this but it only works for the first row.
CY% =

VAR _myPercent =
CALCULATE(
SUM(Production[Value]),
FILTER(ALL(Production), Production[Category] = "Helmets"),
FILTER(Production, Production[Week] <= Max(DatesTable[CurrentWeek])),
FILTER(Production, Production[Year] = Max(DatesTable[CurrentYear]))
)
Return

CALCULATE(
SUM(Production[Value]),
FILTER(Production, Production[Week] <= Max(DatesTable[CurrentWeek])),
FILTER(Production, Production[Year] = Max(DatesTable[CurrentYear]))
)
/

myPercent

------------------------------
Mike McLean
------------------------------

• #### 2.  RE: Calculate the percentage of a column based on one of the values in the column

Posted 16 days ago
Veja se ajuda

``````CY% =
DIVIDE (
SUM ( Tabela[Sales Qty] ),
CALCULATE ( SUM ( Tabela[Sales Qty] ), Tabela[Category] = "Helmets" )
)​``````

------------------------------
Vilmar Santos
------------------------------

• #### 3.  RE: Calculate the percentage of a column based on one of the values in the column

Posted 16 days ago

When I try that it still only works for the first column.  Here is what I used based on your suggestion:

CY% =
DIVIDE(
CALCULATE(
SUM(Production[Value]),
FILTER(Production, Production[Week] <= Max(DatesTable[CurrentWeek])),
FILTER(Production, Production[Year] = Max(DatesTable[CurrentYear]))
),
CALCULATE(
SUM(Production[Value]),
FILTER(Production, Production[Category] = "Helmets"),
FILTER(Production, Production[Week] <= Max(DatesTable[CurrentWeek])),
FILTER(Production, Production[Year] = Max(DatesTable[CurrentYear]))
)
,0
)

------------------------------
Mike McLean
------------------------------

• #### 4.  RE: Calculate the percentage of a column based on one of the values in the column

Posted 15 days ago
Try not to use multiple FILTER. If it doesn't work, attach your pbix please, it can be with dummy datas

``````CY% =
DIVIDE (
SUM ( Production[Value] ),
CALCULATE (
SUM ( Production[Value] ),
FILTER ( Production, Production[Category] = "Helmets" )
),
0
)​``````

------------------------------
Vilmar Santos
------------------------------

• #### 5.  RE: Calculate the percentage of a column based on one of the values in the column

Posted 13 days ago

I was able to get it working.  Here is the solution:

``````CY% =

VAR _myPercent =
CALCULATE(
SUM(Production[Sales]),
FILTER(ALLSELECTED(Production), Production[Category] = "Helmets"),
FILTER(ALLSELECTED(Production), Production[Week] <= Max(DatesTable[CurrentWeek])),
FILTER(ALLSELECTED(Production), Production[Year] = Max(DatesTable[CurrentYear]))
)
Return

CALCULATE(
SUM(Production[Sales]),
FILTER(Production, Production[Week] <= Max(DatesTable[CurrentWeek])),
FILTER(Production, Production[Year] = Max(DatesTable[CurrentYear]))
)
/
_myPercent``````

------------------------------
Mike McLean
------------------------------