Power BI 보고서 작성 # 6
목표 과제 : 세 페이지의 보고서 만들기
테이블에서 쓰인 측정값 그대로 그래프에 쓰였기 때문에 테이블의 측정값들 먼저 생성함
Type Row는 임의로 생성한 테이블의 값으로 아래 이미지와 같이 입력하여 생성함
Index는 정렬을 위해 생성
++ 인덱스는 직접 입력해도 되지만 파워쿼리에 인덱스 열 생성을 통해 쉽게 생성할 수 있음
전체적인 큰 틀은 행에 생성한 Type_Row , 열에는 Dimension의 Calendar , 값에 측정값 @TableData로 이루어져있음
슬라이서가 관계 설정이 되어있지 않은 Calendar Target 테이블로 되어있었고 나머지 Region과 Customer , Tech의 연결에 대한 것은 따로 추후에 모델링을 통해 해결함
@Table Data는 해당하는 Type_Row의 데이터에 맞추어 월별 해당되는 측정값을 반환하도록 다음과 같이 작성함
빈 행을 만들기 위해
Null값을 가지는 데이터를 포함한 컬럼을 생성한 뒤 If 문으로 Null값인 경우(isblank) 빈값이 나오도록 하였음
이때 null 이라고 입력해야 null 값인 것을 인식함
○ 슬라이서로 선택한 날짜
슬라이서로 선택한 것은 FY (회계연도) , Month(월) 이지만 해당하는 행의 Datekey , YYYYMM 같은 값을 반환한다.
아래와 같이 Calendar Target의 YYYYMM 값은 FY22 와 4월을 선택하였을때 , 202204가 반환된다.
굳이 FY의 데이터와 Month의 데이터를 변환 후 조합하여 연월을 구할 필요가 없다.
○ 선택 월 기준으로 다른 측정값 반환하기
회계연도를 처음 접해서 헷갈렸다.
지금의 데이터 기준 회계연도 FY22는 2021년 10월부터 2022년 9월까지임
따라서 2021년 10월부터 2022년 4월까지는 act 값 , 2022 5월부터 9월까지는 olk 값을 반환하고자 할때
처음에는 굳이 실제 날짜를 기준으로 생각하여 전년도를 구하는 식에 월을 10으로 나눈 나머지를 통해 4월보다 크고작음을 판별하려고하였으나 그렇게 어렵게 할 필요없이
회계연도가 2022년이고 , 회계월 ( 10월부터 1 , 11월 2 .... ) 을 기준으로 4월( 회계월 7) 보다 작거나 큰 것으로 나누어 반환하면 된다.
두가지 Fact Table에서 각각 act 값과 olk 값을 구하는 식을 변수로 지정하였고 다음과 같이 Calendar Target의 FY Sort(회계월) 기준으로 비교하여 값을 반환하였다.
@ACT_OLK =
IF(
SELECTEDVALUE('Calendar'[FY Sort]) < SELECTEDVALUE('Calendar Target'[FY Sort]),
CALCULATE(
SUM(Actual[Units Sold]),
'Actual'[FY Num] = VALUE(SELECTEDVALUE('Calendar Target'[FY Num])),
'Actual'[Month] = SELECTEDVALUE('Calendar'[Month Num]),
Actual[Apply flag] = 1
),
CALCULATE(
SUM('Plan'[Value]),
'Plan'[Type] = "Outlook",
'Plan'[Work Month] = VALUE(MAX('Calendar Target'[YYYYMM]))
))
이때 해당하는 월이 없는 합계값이 반환되지 않기 때문에 합계값을 반환하는 것을 추가하여 수정하였다.
합계의 경우에는 act에 들어있는 필터 중 선택된 월에 따라 반환하는 부분을 제거하고
선택한 회계 월 보다 작은 경우의 값들의 합을 반환하게 함.
Olk는 선택한 회계월보다 큰 경우의 값들의 합이 반환되어 그 합계가 구해짐.
HASONEVALUE를 통해 월 유무 판단함
(HASONEVALUE( column ) : 하나의 고유값으로 필터링된 경우 true)
○ 전년도의 값 반환하기
처음에는 아래와 같이 calendar Target의 선택된 회계연도 기준 전년도를 구하였다.
○ 날짜 키 가져오기 ( Dimension값과 Fact 값 비교 )
Pace의 Datekey와 비교하는 필터 있어야함 >> calendar로 하면 전체 값 나옴
'Plan'[Work Month] = VALUE(LEFT(SELECTEDVALUE('Pace'[DateKey]),6))
필터 조건에서 date와 같이 dimension 테이블이 있는 경우, fact 테이블의 값이 아닌 Dimension 테이블의 값을 통해 조건을 걸어준다
처음에는 아래와 같이 fact 테이블인 Pace의 Datekey를 사용하여 조건을 걸어주었다.
이렇게 해도 잘 적용이 된다.
2022년 5월을 슬라이서로 선택했을때 슬라이서의 Calendar 테이블과 Pace 테이블이 연결관계가 있기 때문에
그리고 DateKey 열의 값의 연도월 값을 Plan 테이블의 work month 컬럼과 비교하여 2022년 5월인 경우의
Value 만 Sum 하여 값을 가져오기 때문이다.
@Pace_OLK =
CALCULATE(
SUM('_Plan'[Value]),
'_Plan'[Type] = "Outlook",
'_Plan'[Work Month] = VALUE(LEFT(SELECTEDVALUE('_Pace'[DateKey]),6))
)
두번째로 fact 테이블이 아닌 Dimension 테이블인 Calendar 테이블을 가지고 필터를 걸게되면
값은 제대로 나오지만 2022년 5월이 아닌 값도 나온다.
@Pace_PP =
CALCULATE(
SUM('_Plan'[Value]),
'_Plan'[Type] = "PP",
'_Plan'[Work Month] = VALUE(SELECTEDVALUE('Calendar'[YYYYMM]))
)
이때 해결 방법은 측정값 해결방법과 filter를 걸어주는 방법 두가지가 있는데
우선 측정값으로 해결하는 방법은 아래와 같이 if문을 통해 먼저 조건을 걸어주어 Pace의 datekey가 2022년 5월인 경우만 표시되게 하고 나머지는 동일하게 적용한다.
@Pace_PP =
IF(
LEFT(SELECTEDVALUE('_Pace'[DateKey]),6) = SELECTEDVALUE('Calendar'[YYYYMM]),
CALCULATE(
SUM('_Plan'[Value]),
'_Plan'[Type] = "PP",
'_Plan'[Work Month] = VALUE(SELECTEDVALUE('Calendar'[YYYYMM]))
)
)
필터를 사용하는 방법은 Pace 테이블의 데이터를 사용한 측정값인 Pace Cm(Volumn)을 공백이 아닌 값만 표시하도록 하는 것이다.
Pace Cm 값은 Pace 테이블과 연결관계가 있기에 2022년 5월의 값만 나오고 나머지 날짜에 대해서는 공백이기 때문에 필터를 적용하면 원하는 값만 가져올 수 있다.
○ 다음 달의 측정값
Target Month Num 으로 구분되어있어 Target Month Num과 동일하면 current month , 아니면 next month 값이 된다.
@Volumn_NM =
CALCULATE(
SUM('_Pace'[Volumn]) ,
'_Pace'[DateKey] = SELECTEDVALUE('_Pace'[DateKey]),
'_Pace'[Target Month Num] <> SELECTEDVALUE('Calendar'[Month Num])
)
○ Dateadd 중복
NM 테이블에서 py는 2021 6월 값 기준이다 (2021년 5월을 선택했을때 다음 월이므로)
Dateadd를 두번중복해서 한 필터 안에 쓸수 있는지 모르겠음
적용했을때 값이 나오지 않음
변수로 전년도의 날짜를 지정하고 이에 한달 뒤 dateadd를 적용하니 값이 나옴
@Pace_PY_NM =
VAR py = DATEADD('Calendar'[Date] ,-1, YEAR)
RETURN
CALCULATE(
SUM(_Actual[Units Sold]),
DATEADD(py ,1, MONTH),
LEFT('_Pace'[DateKey], 6) = SELECTEDVALUE('Calendar'[YYYYMM]),
_Actual[Apply flag] = 1
)
○ 모델링
다대다 연결이 아니게 하려면
Dimension 테이블에는 고유값(하나밖에없는 값)으로 이루어진 컬럼과 그 고유값들이 있는 Fact 테이블의 컬럼끼리 연결해야함
만약 Fact 테이블에 고유값으로 이루어진 컬럼이 없다면 생성해야함
따라서 Fact 테이블에 Cust_Tech Key를 Customer 와 Tech의 데이터를 병합하여 생성함
이후 테이블간의 연결관계를 생성해주니 슬라이서의 선택에 따라 차트의 값이 변화하는 것을 확인할 수 있었음
