SCM 이란?
Supply Chain Management (공급망 관리) 의 약자로,
생산자, 공급자, 고객에 이르는 물류의 흐름을 하나의 가치사슬 관점에서 파악하고,
공급망의 구성요소들 사이에서 이루어지는 전체 프로세스의 최적화를 달성하고자 하는 기법
실습 준비
데이터셋
https://www.kaggle.com/datasets/bhanupratapbiswas/inventory-analysis-case-study
위 데이터셋에서, 아래 다섯개를 사용
목표
Power BI로 SCM 프로세스별 모니터링용 대시보드 만들기
- 구매 데이터 : 판매자별 실적 모니터링
- 재고 데이터 : 기초재고와 기말재고 차이(Gap) 모니터링
- 고객 판매 데이터 : 날짜 및 요일별 판매 트렌드 모니터링
기초재고 + 입고 - 출고 = 기말재고
Vendor 대시보드 시각화
DAX로 대시보드를 위한 값 계산하기
- Vendor count
V1_Vendor Count = DISTINCTCOUNT(InvoicePurchases[VendorName])- PO count
V2_PO Count = DISTINCTCOUNT(InvoicePurchases[PONumber])- Brand count
V3_Brand Count = DISTINCTCOUNT(Purchases[Brand])- Average Qty per PO (PO 당 평균 몇 개의 상품을 갖는지)
V4_Avg Qty per PO = DIVIDE(sum(InvoicePurchases[Quantity]), [V2_PO Count])- Average Amt per PO (PO 당 평균 금액)
V5_Avg Amt per PO = DIVIDE(sum(InvoicePurchases[Dollars]), [V2_PO Count])- Average Freight per PO (PO 당 평균 운송 금액)
V6_Avg Freight per PO = DIVIDE(sum(InvoicePurchases[Freight]), [V2_PO Count])상위 5개 vendor만 여러 행 카드에 필터링
DAX : DATEDIFF
날짜 간의 간격 계산하기
기본 문법
DATEDIFF(<Date1>, <Date2>, <Interval>)Interval에 들어갈 수 있는 항목
예시
PODate와 ReceivingDate 사이의 일수를 세어서 새로운 열 PO_RCV_DATEDIFF에 저장하기
PO_RCV_DATEDIFF = DATEDIFF(Purchases[PODate], Purchases[ReceivingDate], DAY)PODate부터 PayDate까지 며칠이 걸리는지
PO_Pay_DATEDIFF = DATEDIFF(Purchases[PODate], Purchases[PayDate], DAY)결과
Vendor 대시보드
Inventory 대시보드
두 테이블을 하나의 테이블로 병합하기
2가지 방법이 있는데,
첫 번째 :
테이블1의 아래에 테이블2를 붙여넣고 싶다면 (=병합)
데이터 변환 창 들어가서,
새 원본 > 빈 쿼리 > 쿼리문에 '= 테이블1' 쓰기
결합 > 쿼리추가 클릭, 병합할 테이블(테이블2) 선택 및 확인 누르기
두 번째 :
쿼리문에 = Table.Combine({테이블1, 테이블2}) 쓰기
결측치 채우기
데이터 변환 창으로 들어가서,
결측치를 채울 열에 마우스 우클릭 > 값 바꾸기 클릭
이후 뜨는 창에서 바꾸기 전/후 값을 차례대로 넣어주면 됨
DAX로 새 값 계산하기
- Store Count
Inv1_Store Count = DISTINCTCOUNT(Inventory[Store])- Quantity
Inv2_Inventory Quantity = sum(Inventory[quantity])- Amount
Inv3_Amount = sum(Inventory[Amount])Waterfall Chart란?
- 비교하고자 하는 컬럼이 어떤 요인으로 차이가 발생하는지를 표현하는 시각호 ㅏ방법
- X축에 요인이 되는 항목을 선정함 (시계열, 카테고리 등)
예시 1
예시 2
시작점은 맨 처음 재고, 종료점은 기말재고를 두어서,
어떤 매장에서 재고가 많이 늘어났는지 한 눈에 볼 수 있음
예시 3
분기마다의 누적값을 표시해서, 언제 얼마나 증감했는지 파악하기에 용이함
POWER BI 적용 예시
(정렬 오름차순, 데이터레이블 키고 분석결과 20개로 늘림)
매개변수 슬라이서
아래 사진처럼 선택한 컬럼을 인터랙티브하게 보여주고 싶다면, 매개변수를 추가하고, 그래프의 X축 or Y축 or 값 or 범주 등에 넣어주면 됨
여기선 Waterfall차트의 Y축에 넣어주겠음
상단의 모델링 탭 > 매개변수 > 필드 클릭
아래 사진처럼 슬라이서로 사용할 컬럼 선택해서 넣어주고, '만들기' 클릭
생성한 매개변수를 그래프의 Y축에 넣어주면 끝~
Sales 대시보드
Left Join 방법
데이터 변환 창으로 들어가서,
1 상단의 결합 > 쿼리 병합 클릭,
2 병합할 테이블 선택
3, 4 키로 사용할 컬럼을 각 테이블에서 클릭
5 조인 종류 선택
확인 누르기
생성된 컬럼의 테이블 표시를 눌러서, 가져올 컬럼 선택하면 행 데이터가 Table에서 원하는 값으로 변경됨
COALESCE (특정 컬럼의 빈 값을 다른 컬럼의 값으로 대체)
아래 코드는 새 열을 생성할 때 PurchasePrice 컬럼을 그대로 가져오되, PurchasePrice가 빈 값이면 SalesPrice값을 가져오라는 뜻
PurchasePrice_F = COALESCE(Sales[PurchasePrice], Sales[SalesPrice])이익 (Profit) 구하기
해당 실습에서는 다른 자세한 비용은 가정하지 않으므로,
이익 = 판매가 - 매입가
Profit = sum(Sales[SalesPrice]) - sum(Sales[PurchasePrice_F])이익을 총 매입가 대비 비율로 환산하기
Profit_Pct = DIVIDE([Profit], sum(Sales[PurchasePrice_F]))요일별 분석
달력 테이블 만들기
SalesDate의 최솟값부터 최댓값까지 매일을 담는 테이블을 생성함
D_Calendar = CALENDAR(MIN(Sales[SalesDate]), max(Sales[SalesDate]))결과
열 누르고, 서식에
yyyy-mm-dd ddd와 같이 써서, 아래 사진같이 만들기
- dddd : 월요일, 화요일, 수요일 ... 처럼 표기
- ddd : 월, 화 수, ... 처럼 표기
요일만 추출하기 (숫자)
Weekday = WEEKDAY(D_Calendar[Date], 1) -- 일요일부터 1요일만 추출하기 (텍스트)
Weekday_eng = if(D_Calendar[Weekday]=1, "Sun", if(D_Calendar[Weekday]=2, "Mon", if(D_Calendar[Weekday]=3, "Tue", if(D_Calendar[Weekday]=4, "Wed", if(D_Calendar[Weekday]=5, "Thu", if(D_Calendar[Weekday]=6, "Fri", if(D_Calendar[Weekday]=7, "Sat")))))))Sales 테이블과 D_Calendar 테이블 조인하기
꺾은 선형으로 요일별 패턴 파악 (-> 금요일마다 판매량과 판매수익 증가)
매일 평균 판매량 구하기
S1_Daily Avg Sales Qty = DIVIDE(sum(Sales[SalesQuantity]), DISTINCTCOUNT(Sales[SalesDate]))매일 평균 판매 수익 구하기
S2_Daily Avg Sales Amt = DIVIDE(sum(Sales[SalesDollars]), DISTINCTCOUNT(Sales[SalesDate]))매일 평균 판매 단가 구하기
S3_Daily Avg Sales Price = DIVIDE([S2_Daily Avg Sales Amt], [S1_Daily Avg Sales Qty])테이블로 자세한 데이터 파악 (일자별)
테이블로 자세한 데이터 파악 (요일별)
보조 Y축 0부터 정렬하도록 변경
변경 전
변경 후
계기 차트
달성률을 모니터링할 때 편리한 차트
- 값
- 최소/최대값
- 대상 (목표)
KPI 시각화
측정 가능한 목표에 대해 만든 진행률의 정도를 알려주는 시각화 방법
단, 계기 차트와 다르게 '대상'에 그냥 정수값을 넣으면 아래처럼 오류가 남
날짜 데이터가 같이 있어야 하는 듯
AVERAGEX 함수 사용법
AVERAGEX(테이블, 계산식)
-- 테이블은 보통 날짜 테이블을 넣는 듯아래와 같은 데이터가 있다고 가정!
매출 데이터가 하루에 하나씩만 있는게 아니라, 하루에 여러 건이 있는 경우가 많음.
이 매출 데이터에 대해서 그냥 평균을 내버리면, 일자별 특성을 무시하게 됨
따라서, 매출의 '일 평균'을 구한 후, 그 일 평균에 대해서 AVG를 구하는 게 AVERAGEX 라고 할 수 있음.
'일' 평균이 필요하면, AVERAGEX의 첫 번째 인자(테이블)에 매일이 기록된 날짜 테이블을 넣어주면 됨.
이때, 보통 날짜 컬럼을 기준으로 많이 하는데, 날짜 컬럼을 넣을 때는 그냥 넣는게 아니라 VALUES()로 묶어주어야 함.
VALUES로 날짜 컬럼을 묶으면, 아래처럼 하나의 임시 테이블 형태로 만들어줌
이렇게 일별/주별로 평균을 내면 아래와 같이 값을 다르게 확인할 수 있음
(출처 : https://youtu.be/Fl5AqgjTbc0?si=r16169VLbJ9YNqk4)
AVERAGEX, ALL, FILTER로 원하는 요일 + 일자 필터링
Weekday Sales Goal =
CALCULATE(
AVERAGEX(VALUES(D_Calendar[Date]), [S1_Daily Avg Sales Qty]), -- 일평균 Sales Qty
ALL(Sales[SalesDate]), ALL(D_Calendar[Date]), -- ALL로 모든 필터 초기화
FILTER(D_Calendar, D_Calendar[Weekday] = WEEKDAY(max(Sales[SalesDate]))), -- 가장 최근 날짜의 요일에 대해서만 보여주어라
FILTER(D_Calendar, D_Calendar[Date] < max(Sales[SalesDate]))) -- 단, 오늘 날짜 데이터는 제외해라 (아직 오늘 매출이 모두 집계되지 않았다고 가정)이렇게 만든 (오늘 요일의) 일평균 매출 데이터를 KPI 시각화의 목표에 넣으면,
이렇게 요일 평균 매출 대비 달성률이 얼마인지 확인 가능!
추가로, 슬라이서로 날짜 조정할 수 있게 넣어도 봄
🔵 흥미로웠던 점:
파이썬이나 SQL로 직접 테이블을 조작하는 과정과 비슷해서 배우기 보다 수월했다. 그치만 비슷한 듯 용어가 달라서 잘 짚고 넘어가야 했음!
🔵 다음 학습 계획:
pyspark를 활용하여 대규모 판매 데이터를 분석하는 과정을 배울 것입니다.
