Power BI × Google 스프레드시트(GAS)로 매출·재고·매출총이익을 가시화하기【실전 가이드】
「매월 집계에 시간이 걸린다」 「매출총이익이나 체화 재고가 바로 보이지 않는다」——그런 고민은, Power BI와 Google 스프레드시트(GAS)의 조합으로 해결할 수 있습니다. 이 글에서는 스프레드시트를 데이터의 입구로 삼아, 매출·재고·매출총이익을 자동으로 가시화하기까지의 흐름을, 8가지 단계로 쉽게 해설합니다.
※본 기사에는 제휴 링크(아마존 어소시에이트)를 포함합니다.
STEP 01 가시화의 목표를 정한다
가장 먼저 정하는 것은 「무엇을·누가·어느 정도의 빈도로 보고 싶은가」입니다. 예를 들어 『경영자가 월별로 매출과 매출총이익의 추이를 본다』 『현장이 매주 체화 재고를 확인한다』처럼, 보는 사람과 목적을 분명히 합니다. 목표가 정해지면, 모을 데이터와 만들 그래프가 자연스럽게 정해집니다.
포인트 공들인 대시보드보다, 우선 『매일·매월 반드시 보는 숫자』를 1~2개 정하는 편이 오래갑니다.
STEP 02 데이터를 모은다(스프레드시트 × GAS)
매출·재고·매입 등의 데이터를 Google 스프레드시트에 집약합니다. 수기 입력뿐 아니라, GAS(Google Apps Script)를 쓰면 외부 시스템이나 메일·API에서 자동으로 시트에 기록할 수도 있습니다. 「데이터가 한곳에 자동으로 쌓이는」 상태를 만드는 것이, 이후 자동화의 토대가 됩니다.
STEP 03 분석할 수 있는 형태로 정리한다(정규화)
사람이 보기 좋은 표(항목이 가로로 펼쳐지는 형태)는, 기계에게는 다루기 어려운 형태입니다. 분석에서는 날짜·상품·수량·금액 등을 열로 갖는 「세로형」으로 변환합니다. 표기 흔들림(전각과 반각, 상품명의 흔들림 등)을 통일하고, 상품 코드 같은 키가 되는 열을 준비해 두면, 이후 데이터 모델이 깔끔하게 연결됩니다.
STEP 04 Power BI에 연결한다
정리한 스프레드시트는, Power BI에서 직접 연결해 가져올 수 있습니다. 공유 링크나 커넥터를 통해 읽어 두면, 스프레드시트를 갱신하고 Power BI를 새로고침하는 것만으로 최신화할 수 있습니다. 정기 새로고침을 설정하면, 대시보드는 자동으로 새로운 숫자로 바뀝니다.
포인트 가져오기는 「새로고침 버튼 하나로 최신화할 수 있는」 상태로 해 두면, 운용이 이어집니다.
STEP 05 데이터 모델을 설계한다(스타 스키마)
Power BI의 실력은 데이터 모델을 만드는 방식으로 결정됩니다. 매출이나 재고 등의 「사실(팩트)」을 중심으로, 상품·날짜·매장 등의 「기준(디멘션)」을 주위에 배치하는 스타 스키마로 하면, 집계가 빠르고 지표도 만들기 쉬워집니다. 예를 들어 매출 명세의 팩트에, 상품 마스터(원가나 카테고리)와 날짜 테이블을 연관 짓습니다.
포인트 「팩트(세는 대상)」와 「디멘션(기준)」을 나눈다——이것이 토대입니다.
STEP 06 지표를 만든다(DAX로 매출·매출총이익)
숫자의 정의는 DAX(Power BI의 계산식)로 만듭니다. 매출이나 수량의 합계는 물론, 원가와 대조해 매출총이익이나 이익률을 계산하고, 월·상품·카테고리 등 임의의 기준으로 자동 집계할 수 있습니다. 한 번 제대로 정의하면, 어느 그래프에서도 같은 정의의 숫자를 쓸 수 있습니다.
Gross Profit =
SUMX (
FactSales,
FactSales[Quantity] * ( FactSales[UnitPrice] - RELATED ( DimProduct[Cost] ) )
)매출 명세별로 매출총이익을 계산하고 합계하는 예(DAX).
STEP 07 재고의 체화를 가시화한다(에이징 분석)
재고는 갖고 있는 것만으로 비용이 됩니다. 입고로부터의 경과 일수로 「0–30일/31–60일/61–90일/90일 초과」 등의 구분으로 나누면, 체화 재고가 한눈에 보입니다. DAX의 SWITCH(TRUE()) 패턴을 쓰면, 경과 일수에 따른 구분을 동적으로 붙일 수 있습니다.
Aging Bucket =
SWITCH (
TRUE (),
[DaysElapsed] <= 30, "0-30일",
[DaysElapsed] <= 60, "31-60일",
[DaysElapsed] <= 90, "61-90일",
"90일 초과"
)경과 일수로부터 체화 구분을 만드는 예(DAX).
포인트 체화 재고를 색으로 구분하는 것만으로, 할인이나 발주 재검토의 판단이 빨라집니다.
STEP 08 대시보드화와 운용
마지막으로, 매출 추이·상품별 매출총이익·재고 에이징 등을 한 화면의 대시보드로 정리합니다. 관계자에게 공유하고 정기 새로고침을 설정하면, 매월의 집계 작업 자체가 불필요해집니다. 운용에서 중요한 것은, 지표의 정의를 하나로 통일하는 것과, 입력 규칙(표기나 필수 열)을 정해 두는 것입니다.
포인트 만들고 끝내지 말고, 「새로고침이 자동으로 도는」 상태까지 만들어 두면, 계속 쓰이게 됩니다.
정리
Power BI와 스프레드시트(GAS)를 조합하면, 매월의 집계 작업을 「자동으로 최신화되는 대시보드」로 대체할 수 있습니다. 갑자기 전부를 만들려 하지 말고, 우선 보고 싶은 숫자를 하나 정해 작게 만들어 보는 것을 추천합니다.
이해를 더하는 도서(PR)
여기까지의 내용을 직접 시도해 보고 싶은 분께, 첫 한 권으로 도움이 되는 도서를 소개합니다.
도서(PR) Power BI 입문서 처음 하는 가시화·리포트 작성에.
도서(PR) DAX 해설서 매출·매출총이익 등 지표 만들기를 깊이 이해하고 싶은 분께.
도서(PR) Google Apps Script 입문 스프레드시트로의 데이터 수집을 자동화하고 싶은 분께.
도서(PR) 데이터 시각화·대시보드 설계 「전달되는」 대시보드 만드는 법을 배우고 싶은 분께.
면책: 본 기사는 일반적인 기술 가이드이며, 동작이나 화면은 버전·환경에 따라 다를 수 있습니다. 제휴: Robin Planning은 아마존 어소시에이트 프로그램 참가자이며, 링크를 통한 구매로 소개료를 받을 수 있습니다.
댓글