업무의 성과를 정량적으로 측정하기 위해 증가율, 성장률, 달성률 등의 KPI를 설정하고 평가합니다. 이는 생산, 매출, 영업 등 다양한 분야에서 활용되며 비율(%)로 표시하거나 수치 그 자체를 나타내기도 합니다.
아래와 같이 월간 목표와 실적현황 데이터가 있는 경우가 대표적인 유형인데 이 데이터를 차트를 이용해서 시각화하는 것은 엑셀에서도 크게 어려운 작업이 아니고 기호에 따라 다양하게 표현도 가능합니다.
월 단위 KPI의 한계
1월 매출목표, 5월 생산목표 등 비교대상 지표가 월 단위인 경우가 많습니다. 월보다 작은 단위는 설정과 평가 모두 부담스러울 수 있고, 월보다 큰 단위는 평가의 실효성이 떨어질 수 있기 때문에 월단위 지표가 많을 것으로 생각됩니다. 다만, 월단위 KPI는 분석이 제한적일 수밖에 없습니다. 주간 또는 일일 실적도 월간 목표 대비 달성률이 측정될 수밖에 없기 때문입니다.
11월 1일 10을 달성했다면, 월 목표대비 약 3.3%를 달성했다고 계산할 수 있습니다. 그런데 더욱 세밀한 관리가 필요한 비즈니스라면 이 지표를 통해서 적정성 여부를 판단하기가 어렵습니다. 11월 10일경에 누적 달성률이 40%라면 잘 하고 있다고 판단하고, 15일 경에 45%라면 다소 부족하다고 판단할 수는 있지만 당장 오늘 실적의 좋고 나쁨을 판단하기는 어렵습니다.
월간 목표를 일일 목표로 변환한다면 분석의 관점을 더욱 다양하고 세밀하게 만들 수 있습니다. 분석이 세밀해 질수록 어떤 행동을 해야 하는지에 대한 의사결정의 정확도가 높아집니다.
월 단위 지표를 일, 주 등의 작은 지표로 세분화하는 방법은 여러가지가 있겠지만, 파워 쿼리(Power Query, M Language)로 일 수에 따라 균등하게 안분하여 산출하는 방법을 소개합니다. 이는 매월 새로 생성되는 목표를 자동으로 일 목표로 변환하고 이를 데이터 모델에 추가하여 대시보드에 자동으로 반영되어서 다양한 필터 컨텍스트에서 활용할 수 있는 것을 목표로 하는 과정의 첫번째입니다.
월 목표를 일 목표로 변환하기 : Power Query
실습 파일 바로가기 (다운로드)
1. 월간 목표 테이블을 Power Query 편집기로 가져오기
예제의 [목표] 테이블은 엑셀 표 형식으로 되어 있습니다.
이 표의 내부 아무 곳이나 선택한 후 [데이터] 탭 > [데이터 가져오기 및 변환] 그룹 > [테이블/범위에서] 를 선택합니다.
테이블을 가져오면 Power Query 편집기가 실행되고 #"원본" 쿼리와 #"변경된 유형" 쿼리가 실행됩니다. #"변경된 유형" 쿼리는 데이터 형식을 자동으로 검색 판단하여 적절한 유형으로 변경하는 것으로 이 단계를 삭제하고 사용자가 판단하여 유형을 설정하는 것이 좋지만, 여기에서는 자동 설정된 '정수' 유형이 적절하므로 그대로 진행합니다.
= Excel.CurrentWorkbook(){[Name="목표"]}[Content]
= Table.TransformColumnTypes(원본,{{"월", Int64.Type}, {"목표", Int64.Type}})2. '월' 열의 속성을 날짜(목표 시작일)로 변환
현재 '월' 열은 단순한 숫자 데이터입니다. 예제의 열 추가 기능을 이용해서 '목표 시작일' 이라는 날짜 속성의 열을 생성합니다. '목표 시작일'은 해당 월의 첫째날입니다.
- "월" 열을 선택하고 [열 추가] 탭 > [일반] 그룹 > [예제의 열] 아이콘 아래 삼각형 부분을 클릭하고 "선택 항목에서"를 선택합니다.
- 오른쪽에 병합됨 부분의 첫번째 행(1월)에 우리가 얻고자 하는 날짜 "2020-1-1" 을 입력하고 Enter를 누릅니다.
이어서 다음 행(2월)에 "2020-2-1"을 입력하고 나서 우측 상단 확인 버튼을 누릅니다.
= Table.AddColumn(#"변경된 유형", "병합됨", each Text.Combine({"2020-", Text.From([월], "ko-KR"), "-1"}), type text)
3. '병합됨' 열 이름 변경
'병합됨' 이라는 이름으로 생성된 새로운 열에서 마우스 오른쪽 버튼을 누르고 [이름 바꾸기]를 선택하여 이름을 '목표 시작일' 로 변경합니다. 열 제목을 더블 클릭해도 이름을 바꿀 수 있습니다.
= Table.RenameColumns(#"삽입된 병합된 열",{{"병합됨", "목표 시작일"}})
4. '목표 시작일'의 속성을 날짜로 변경
"목표 시작일" 열 제목의 앞에 'ABC' 아이콘을 클릭하여 [날짜]를 선택하면 해당 열의 형식이 날짜로 변경됩니다. 열 제목에서 우클릭하여 [형식 변경]을 선택하거나, 메뉴 [변환] 탭 > [열] 그룹 > [데이터 형식] 에서도 변경할 수 있습니다.
= Table.TransformColumnTypes(#"이름을 바꾼 열 수",{{"목표 시작일", type date}})
5. 월별 일 수 구하기
'목표 시작일'의 속성을 날짜로 변경했으니 '목표 시작일'로부터 해당 월의 일 수를 구할 수 있습니다.
'목표 시작일' 열을 선택하고 메뉴에서 [열 추가] 탭 > [날짜 및 시간에서] 그룹 > [날짜] > [월] > [월간 일자] 를 선택하면 해당 날짜가 속하는 월의 일 수를 나타내는 열이 추가됩니다.
= Table.AddColumn(#"변경된 유형1", "월간 일자", each Date.DaysInMonth([목표 시작일]), Int64.Type)
6. 일 목표 구하기
'목표' 열을 '월간 일자' 열의 값으로 나누면 일간 목표가 산출됩니다.
'목표' 열을 선택하고, Ctrl 버튼을 누르고 '월간 일자' 열을 선택합니다.
(반드시 '목표' 열을 먼저 선택해야 합니다. 반대로 선택하게 되면 반대의 나눗셈 결과가 구해지므로 주의해야 합니다)
= Table.AddColumn(#"삽입된 월간 일자", "나누기", each [목표] / [월간 일자], type number)
7. 일간 목표 열 이름 바꾸기
'나누기' 열의 이름을 변경합니다. 나누기 열을 선택하고 메뉴에서 [변환] 탭 > [열] 그룹 > [이름 바꾸기] 를 선택합니다.
나누기 열의 제목이 변경가능 상태로 바뀌면 '일 목표' 라고 이력하고 Enter를 눌러줍니다.
= Table.RenameColumns(#"삽입된 나눗셈",{{"나누기", "일 목표"}})
여기까지 작성된 쿼리는 매 월 목표가 추가되면 자동으로 해당 월의 일수에 따른 일별 목표를 산출해 줍니다. 물론, 연도가 고려되지 않아서 이에 대한 정의가 추가로 필요합니다. 다만, 이 쿼리를 데이터 모델에 추가하여 다양한 날짜 필터를 적용하기 위해서는 기간에 포함된 날짜별 일 목표가 필요합니다.
Power Query의 데이터 형식 중 하나인 목록(List)을 활용하여 날짜를 생성하는 방법을 알아보겠습니다.
8. 목표 종료일 열 추가
월 목표액이 적용될 시작일이 포함되는 열이 설정되어 있으므로 적용 종료일 열을 삽입하겠습니다.
'목표 시작일' 열을 선택한 뒤 [열 추가] 탭 > [날짜 및 시간에서] 그룹 > [날짜] > [월] > [월말] 을 선택합니다.
(열이 추가되고 나면 위 3번 또는 7번 단계를 참고하여 열 이름을 '목표 종료일'로 변경합니다)
= Table.AddColumn(#"이름을 바꾼 열 수1", "월말", each Date.EndOfMonth([목표 시작일]), type date)
= Table.RenameColumns(#"삽입된 월말",{{"월말", "목표 종료일"}})
9. 목표 시작일부터 종료일까지의 모든 날짜를 포함하는 List 열 추가
M 언어에서는 목록(List) 형식을 지원합니다.
목표 시작일부터 목표 종료일까지의 모든 날짜를 포함하는 리스트를 생성해 보겠습니다.
메뉴 [열 추가] > [사용자 지정 열]을 선택하면 직접 쿼리문을 입력할 수 있는 창이 팝업됩니다.
여기에 아래와 같이 쿼리 문을 작성합니다.
{Number.From([목표 시작일])..Number.From([목표 종료일])}- Nuber.From 함수
제공된 Value에서 Number를 반환합니다. 이 예제에서는 날짜 Value인 '목표 시작일'열과 '목표 종료일'열을 제공했기 때문에 엑셀과 마찬가지로 해당 날짜의 본래 데이터인 43831, 43832... 등의 숫자를 반환했습니다. - .. 연산자
첫번째 인수부터 두번째 인수까지 연속되어 있음을 나타내는 연산자입니다.
(이에 대한 공식문서를 아시는 분께서는 댓글 부탁드립니다. 도저히 못찾겠어요!)
= Table.AddColumn(#"이름을 바꾼 열 수2", "목표일", each {Number.From([목표 시작일])..Number.From([목표 종료일])})
= Table.ExpandListColumn(#"추가된 사용자 지정 항목", "목표일")
10. '목표일' 열의 List를 새 행으로 확장
'목표일'열 제목의 오른쪽에 있는 아이콘(화살표가 양방향으로 향하는)을 선택하여, [새 행으로 확장]을 선택하면 List가 갖고 있던 값을 모두 새로운 행으로 생성하여 확장시킵니다.
= Table.ExpandListColumn(#"추가된 사용자 지정 항목", "목표일")
11. '목표일' 열 형식을 날짜로 변환
'목표일' 열 제목 좌측의 형식 아이콘을 클릭하여 [날짜]를 선택하여 '목표일' 열의 형식을 날짜로 변경합니다.
형식 변환은 [변환] 탭 > [열] 그룹 > [데이터 형식] 을 선택하여 변경하거나, '목표일'열을 선택하여 마우스 오른쪽 버튼을 눌러서 [날짜]를 선택하여 변환할 수도 있습니다.
= Table.TransformColumnTypes(#"확장된 목표일",{{"목표일", type date}})12. 로드
모든 과정이 끝났습니다.
이제 결과 확인을 위해서 표로 로드합니다.
[홈] 탭 > [닫기] 그룹 > [닫기 및 로드] > [닫기 및 다음으로 로드...] 을 선택합니다.
[데이터 가져오기] 팝업창에서 [표]를 선택하고, 메뉴 하단에 데이터가 들어갈 위치를 [기존 워크시트]로 선택한 뒤 '월간 목표 테이블' 시트의 E1 셀을 선택합니다.
월 목표지표가 일 목표지표로 일 수에 따라서 안분되었고 또 해당 월의 모든 날짜가 행으로 추가된 결과를 확인할 수 있습니다! 다음 편에서는 생성된 쿼리를 데이터 모델에서 활용하는 방법을 소개하도록 하겠습니다. 감사합니다.
전체 쿼리
let
원본 = Excel.CurrentWorkbook(){[Name="목표"]}[Content],
#"변경된 유형" = Table.TransformColumnTypes(원본,{{"월", Int64.Type}, {"목표", Int64.Type}}),
#"삽입된 병합된 열" = Table.AddColumn(#"변경된 유형", "병합됨", each Text.Combine({"2020-", Text.From([월], "ko-KR"), "-1"}), type text),
#"이름을 바꾼 열 수" = Table.RenameColumns(#"삽입된 병합된 열",{{"병합됨", "목표 시작일"}}),
#"변경된 유형1" = Table.TransformColumnTypes(#"이름을 바꾼 열 수",{{"목표 시작일", type date}}),
#"삽입된 월간 일자" = Table.AddColumn(#"변경된 유형1", "월간 일자", each Date.DaysInMonth([목표 시작일]), Int64.Type),
#"삽입된 나눗셈" = Table.AddColumn(#"삽입된 월간 일자", "나누기", each [목표] / [월간 일자], type number),
#"이름을 바꾼 열 수1" = Table.RenameColumns(#"삽입된 나눗셈",{{"나누기", "일 목표"}}),
#"삽입된 월말" = Table.AddColumn(#"이름을 바꾼 열 수1", "월말", each Date.EndOfMonth([목표 시작일]), type date),
#"이름을 바꾼 열 수2" = Table.RenameColumns(#"삽입된 월말",{{"월말", "목표 종료일"}}),
#"추가된 사용자 지정 항목" = Table.AddColumn(#"이름을 바꾼 열 수2", "목표일", each {Number.From([목표 시작일])..Number.From([목표 종료일])}),
#"확장된 목표일" = Table.ExpandListColumn(#"추가된 사용자 지정 항목", "목표일"),
#"변경된 유형2" = Table.TransformColumnTypes(#"확장된 목표일",{{"목표일", type date}})
in
#"변경된 유형2"