Day26_SQL_분석 67‐68_PowerBI - bonniekwon0721/Dataanalytics-study GitHub Wiki

26/MAR/2024

Sales 데이터셋 살펴보기

필터의 “이 페이지의 필터” 에 필터를 걸면 말 그대로 해당 페이지에만 필터가 걸린다.

분해 트리

분해 트리는 판매 수량을 날짜별로 분석할때 유용하며, 높았던 세일즈를 트랙킹하기에 용이하다.

판매가격와 매입가격 1

실제로 벤더에서 사왔을때의 purchase price 대비 sales price가 어땠고 이윤이 어떤지 확인을 하고 싶다.

  • purchase price 는 ‘purchases’ 과 ‘Inventory’ 테이블에 있다.
  • sales price는 ‘sales’테이블에 있다.

→ 두 테이블은 ‘Join’ 혹은 ‘컬럼추가’가 필요해요.

  1. Purchases Price가 들어있는 새로운 Dimension Table을 만든다. (from Inventory Table → Table copy → Join Key 생성) Sales Table과 Join.

  2. Sales Table에 PurchasePrice에 대한 새로운 컬럼을 추가

→ 두 테이블은 Join Key 가 필요해요.

판매가격과 매입가격 2 - 판매가격 Table 생성

1) Purchases Price가 들어있는 새로운 Dimension Table을 만든다. (from Inventory Table → Table copy → Join Key 생성) Sales Table과 Join.

다른열 제거

해당 기능을 사용하면 선택한 열만 남고 다른 열들은 사라지게 된다.

사용자 지정 열

(데이터 변환 → 열추가 → 사용자 지정 열)

현재 있는 열을 활용하여 연결해서 새로운 열을 만들 때 사용하면 유용하다.

[Store]&"_"&[City]&"_"&[Brand]

현재 상태에서 아까 만든 ‘D_Inventory_Price’ 테이블과 ‘Sales’테이블을 ‘InventoryId’를 키로 Join하게되면 ‘다대다’가 뜨는데 이럴 경우는 각 Id당 1개의 값이 아닌 다수의 값이 존재한다.

→ 이때는 이해 관계자들끼리 정리를 해줘야 한다.

→ 혹시 같은 Id에 값이 복수로 존재한다면 보수적으로 ‘최대값’을 적용하여 분석해보자.

데이터 변환 > 변환 > 그룹화 (InventoryId열을 그룹화 하겠습니다.)

Group by 한 결과값이랑 같은 결과값이 나온다.

다시한번 관계에 가서 Join 해봅시다.

다대일 (D_Inventory_Price 테이블의 InventoryId가 1)로 조인이 잘 되었다.

판매가격과 매입가격 3 - 테이블 병합

2) Sales Table에 PurchasePrice에 대한 새로운 컬럼을 추가

쿼리병합

(데이터 변환 > 홈 > 쿼리변환)

: Join Key 를 병합창에서 선택하고, ‘조인 종류’의 경우 ‘left Outer Join’를 이번 경우엔 하겠습니다.

  • 만들어진 병합열을 확장하여 ‘확장’선택 > ‘PurchasePrice’선택 > 확인

데이터 확인

위에서 만들어진 ‘D_Inventory_table’을 살펴보니 Purchase Price의 가격 정보가 없는 것들이 있다.

→ PurchasePrice가 Null 인 경우 Sales Price와 같은 값이라고 정한다. Sales Price를 가지고 와서 적용해주어라. (Profit이 0)

판매가격과 매입가격 4 - 이익 계산

Sales 에 결측값을 보충할 열을 하나 추가하겠습니다.

(열 > 새열)

COALESCE: 빈 값으로 계산되지 않는 첫 번째 인수를 반환합니다. 모든 인수가 빈 값으로 계산되는 경우 Blank 가 반환됩니다.

PurcasePrice_F = Coalesce(Sales[PurchasePrice], Sales[SalesPrice)

역마진과 이익Profit 의 정의는?

  • 역마진: SalesPrice < PurchasePrice
  • Profit = SalesPrice - PurchasePrice

우리는 ‘새 측정값’으로 Sales Table에 Profit을 계산하겠습니다.

Profit = sum(Sales[SalesPrice]) - sum(Sales[PurchasePrice_F])

추가적으로 몇% 정도가 이익인지 보겠습니다.

Profit% = divide([Profit], sum(sales[PurchasePrice_F]))

Dax 1 - Calendar

[Date and time functions (DAX) - DAX | Microsoft Learn](https://learn.microsoft.com/en-us/dax/date-and-time-functions-dax)

[CALENDAR function (DAX) - DAX | Microsoft Learn](https://learn.microsoft.com/en-us/dax/calendar-function-dax)

Date단위로 분석을 하다보니 요일별로 분석해보면 또 다른 분석을 할 수 있을 것이라 판단되었다.

-- 본 쿼리
CALENDAR(<start_date>, <end_date>)

-- 예시 쿼리
= CALENDAR (DATE (2015, 1, 1), DATE (2021, 12, 31))

Calender Table 생성

새로운 테이블을 생성하고 새 열을 추가하겠습니다.

D_Calendar = CALENDAR(min(Sales[SalesDate]), max(Sales[SalesDate]))

그 다음 날짜 적용 서식 설정

Year = YEAR(D_Calendar[Date])
Quarter = QUARTER(D_Calendar[Date])
Month = Month(D_Calendar[Date])
-- Week 가 숫자로 보인다. 
-- 뒤에 '1'을 적용하면 일요일부터 차례대로 1,2,3,...
Weeknum = WEEKDAY(D_Calendar[Date],1)
Weekday = IF(D_Calendar[Weeknum]=1, "Sun", if(D_Calendar[Weeknum]=2, "Mon", if(D_Calendar[Weeknum]=3, "Tue", if(D_Calendar[Weeknum]=4, "Wed", if(D_Calendar[Weeknum]=5, "Thu", if(D_Calendar[Weeknum]=6, "Fri", "Sat"))))))

Sales Table의 SalesDate와 D_Calendar Table의 Date를 연결시켜주자

요일별 판매 트렌드 1

보통의 유통은 주중 주말별 세일즈 패턴이 나타난다.

일 평균 세일즈 Qty 열을 새 측정값으로 만들겠습니다.

S1_Daily AVG Sales Qty = DIVIDE(SUM(Sales[SalesQuantity]), DISTINCTCOUNT(Sales[SalesDate]))

일 평균 Amount 열을 새 측정값으로 만들겠습니다.

S2_Daily AVG Sales Amount = DIVIDE(SUM(Sales[SalesDollars]), DISTINCTCOUNT(Sales[SalesDate]))

Studied from 제로베이스 데이터스쿨

⚠️ **GitHub.com Fallback** ⚠️