경영정보시각화능력 실기 Power BI DAX 함수 수식 작성 문제 정리

경영정보시각화능력 자격증 실기 Power BI의 DAX 함수를 정리했다. 파워 BI에서 DAX 함수는 새 테이블 추가, 새 열 추가, 새 측정값 추가 등 데이터 관련해서 편집할 때 사용된다. 엑셀의 함수와 비슷하다.

이번이 1회라 정보가 별로 없어서 일단 상공회의소 모의문제에 있는 것들 위주로만 공부하고 있다.

 

경영정보시각화능력 파워 BI 함수 출제 범위(2024년 8월 28일 기준)

출처: 상공회의소

DAX 함수 이외 프로그램 구현에 필요한 연산자, 변수, 쿼리 구문

산술 연산이 많이 쓰일 수 있는데, +, -, *, /, ^는 순서대로 더하기, 빼기, 곱하기, 나누기, 지수이다.

자세한 내용은 Power BI 공식 학습 페이지에 자세하게 설명되어 있으니 논리 연산자 같은 거 처음 보는 사람들은 참고하는 게 좋다.

https://learn.microsoft.com/ko-kr/dax/dax-operator-reference

DAX 연산자 - DAX

모의문제에 쓰인 DAX 연산자는 (), *, -, = 정도인 것 같다.

 

수식 빠르게 입력하는 법

몇 글자 치면 목록이 뜨는데 여기서 위아래 화살표로 원하는 함수나 데이터를 찾아서 Tab 키를 누르면 된다.

함수를 입력하면 함수에 대한 설명과 구문, 매개 변수가 나오므로 참고해서 작성하면 된다.

잘 모르는 함수가 나오더라도 최선을 다하자!!

 

측정값 제대로 계산했는지 확인하는 법

테이블을 하나 만들어서 값을 넣어본다.

생뚱맞은 값이 나오면 뭔가 잘못된 것.

 

모의문제 내용 및 풀이 정리

*문제에 서식 관련 내용이 포함된 건 생략하고 수식 작성 관련 항목만 가져왔다.
*DAX 함수는 대소문자 상관없다.

[A형 - 문제1 - 2 - ①]
▶ 테이블 이름: DimDate
- 필드 이름: Date, 연도, 월
- 사용 함수: ADDCOLUMNS, CALENDAR, DATE, YEAR, MONTH
- [Date] 필드의 시작일: 2022-01-01
- [Date] 필드의 종료일: 2022-03-31
- [연도], [월] 필드 : [Date] 필드 기준으로 값 표시

DimDate = ADDCOLUMNS(CALENDAR(DATE(2022,1,1), DATE(2022,3,31)), "연도", YEAR([Date]), "월", MONTH([Date]))

ADDCOLUMNS(테이블, 열 이름, 식, 열 이름, 식, …) : 지정된 테이블이나 테이블 식에 계산 열을 추가한다.

CALENDAR(시작 날짜, 종료 날짜) : "Date"라는 단일 열이 있는 테이블을 반환한다. 이로 인해 ADDCOLUMNS의 매개 변수에 테이블이 필요함에도 불구하고 CALENDAR로 대체할 수 있는 것이다. 입력한 시작 날짜와 종료 날짜를 포함한 기간의 날짜 범위를 반환한다.

YEAR(열 이름): 연도 반환

MONTH(열 이름): 월 반환

CALENDAR로 "Date"라는 열이 만들어졌기 때문에 YEAR과 MONTH에서 "Date"의 날짜를 활용하는 것이다.

[A형 - 문제1 - 2 - ③]
▶ 측정값 이름: 총대여건수
- 활용 필드: <자전거_대여이력> 테이블의 [대여건수] 필드
- [대여건수]의 합계 계산
- 사용 함수: SUM

총대여건수 = SUM('자전거_대여이력'[대여건수])

SUM(): 합계 계산.(엑셀이랑 똑같음)

[A형 - 문제1 - 2 - ③]
▶ 측정값 이름: 일평균 대여건수
- 활용 테이블 및 필드 : <DimDate> 테이블, <자전거_대여이력> 테이블의 [총대여건수] 측정값
- [총대여건수]를 <DimDate> 테이블의 전체 일수로 나누기 계산
- 사용 함수: COUNTROWS

일평균 대여건수 = [총대여건수]/COUNTROWS('DimDate')

COUNTROWS(테이블): 테이블의 행의 수를 반환한다.

나누기 연산자인 '/'를 사용하였다.

[A형 - 문제3 - 5 - ①]
▶ 측정값 이름: 매출_매장
- 활용 필드
  • <판매> 테이블의 [총매출금액] 측정값
  • <거래처> 테이블의 [채널] 필드
- [채널] 필드 값이 “매장”인 경우의 [총매출금액]을 반환
- 사용 함수: CALCULATE, FILTER

매출_매장 = CALCULATE([총매출금액], FILTER('거래처', [채널]="매장")) 

CALCULATE(식, 필터) : 필터에 해당되는 식을 반환한다.

[A형 - 문제3 - 5 - ②]
▶ 측정값 이름: 전월_매출
- 활용 필드
  • <판매> 테이블의 [총매출금액] 측정값
  • <날짜> 테이블의 [날짜] 필드
- 1개월 전의 [총매출금액]을 반환
- 사용 함수: CALCULATE, DATEADD

전월_매출 = CALCULATE([총매출금액], DATEADD('날짜'[날짜], -1, MONTH))

DATEADD(날짜, 더하거나 뺄 간격 수, 이동할 간격): 더하거나 뺄 간격 수에는 정수(-1, -2, 0, 1 등)가 들어간다. 이동할 간격에는 year, quarter, month, day 중 하나가 들어간다. 1개월 전의 값을 반환하라 하였으므로 -1, month를 넣어야 한다. quarter는 분기를 뜻한다.

[A형 - 문제3 - 5 - ③]
▶ 측정값 이름: 연간_누계
- 활용 필드
  • <판매> 테이블의 [총매출금액] 측정값
  • <날짜> 테이블의 [날짜] 필드
- 연간 [총매출금액]의 누계 값을 반환
- 사용 함수: TOTALYTD

연간_누계 = TOTALYTD([총매출금액],'날짜'[날짜])

TOTALYTD(식, 날짜, 필터링, 연말 날짜): 연간 값을 반환하며 뒤의 필터링과 연말 날짜는 생략할 수 있다.

[A형 - 문제3 - 5 - ④]
▶ 측정값 이름: 순위
- 활용 필드
  • <판매> 테이블의 [총수량] 측정값
  • <제품> 테이블의 [제품명] 필드
- [제품명]을 기준으로 [총수량]의 순위를 반환하며, [총수량] 기준 내림차순으로 정렬
- 사용 함수: RANKX, ALL
- [총수량]이 동률인 경우 다음 순위 값은 동률 순위 +1을 한 순위로 표시
  • 예) 2개의 값이 2위인 경우, 다음 값은 3위로 표시

순위 = RANKX(ALL('제품'[제품명]), [총수량], , DESC, Dense)

RANKX(테이블, 식, 값, 순서, 동률): 값을 생략하면 식의 행의 값을 가지고 순위를 매긴다.

ASC = 오름차순(1, 2, 3, )
DESC = 내림차순(10, 9, 8, …)

Skip = 1위가 2개일 때 다음 값은 3위
Dense = 1위가 2개일 때 다음 값은 2위

[B형 - 문제1 - 3 - ①]
▶ 테이블 이름: <요약>
- 활용 필드: <지자체별_방문자수> 테이블의 [광역지자체명], [방문자수] 필드
- <행정구역코드> 테이블의 [광역지자체명] 필드를 기준으로 방문자 수의 합계 반환
- 사용함수: SUM, SUMMARIZE

요약 = SUMMARIZE('지자체별_방문자수', '지자체별_방문자수'[광역지자체명], "합계", SUM('지자체별_방문자수'[방문자수]))

SUMMARIZE(테이블, 기준, 열 이름, 식)

[B형 - 문제1 - 3 - ①]
▶ 측정값 이름: [광역지자체수]
- 활용 필드: <행정구역코드> 테이블의 [광역지자체명] 필드
- [광역지자체명]의 개수 반환
- 사용함수: DISTINCTCOUNT

광역지자체수 = DISTINCTCOUNT('행정구역코드'[광역지자체명])

DISTINCTCOUNT는 빈 값(BLANK)을 포함한 열의 고유값 수를 반환한다. COUNT와 달리 서로 다른 값이 몇 개인지를 계산하는 함수다. 

[B형 - 문제1 - 3 - ②]
▶ 측정값 이름: [서울지역_방문자수]
- 활용 필드: <지자체별_방문자수> 테이블의 [방문자수], [광역지자체명] 필드
- 서울지역 [방문자수]의 합계 반환
- <지자체별_방문자수> 테이블에 적용된 필터 제외
- 사용함수: ALL, CALCULATE, FILTER, SUM

서울지역_방문자수 = CALCULATE(SUM('지자체별_방문자수'[방문자수]), FILTER(ALL('지자체별_방문자수'),'지자체별_방문자수'[광역지자체명]="서울특별시"))

<지자체별_방문자수> 테이블에 적용된 필터를 제외하라 했으므로 ALL()을 사용한다.

[B형 - 문제1 - 3 - ②]
▶ 측정값 이름: [서울방문자비율%]
- 활용 필드: [서울지역_방문자수] 측정값, <요약> 테이블의 [합계] 필드
- 전체 방문자 수의 [합계]에 대한 [서울지역_방문자수]의 비율 반환
- 사용함수: DIVIDE, SUM

서울방문자비율% = DIVIDE([서울지역_방문자수], SUM('요약'[합계]))

비율을 반환하는 것이므로 위와 같이 나온다.

[B형 - 문제3 - 1 - ①]
▶ 측정값 이름: [완전판매건수]
- 활용 필드: <방송주문> 테이블의 [주문번호], [준비수량], [판매수량] 필드
- [준비수량]이 모두 판매된 [주문번호]의 건 수 계산
- 사용함수: CALCULATE, COUNT, FILTER

완전판매건수 = CALCULATE(COUNT('방송주문'[주문번호]), FILTER('방송주문', [준비수량]=[판매수량]))

건 수를 계산하라 했으므로 COUNT를 사용하여 개수를 반환한다.

[B형 - 문제3 - 1 - ②]
▶ 측정값 이름: [총판매금액]
- 활용 필드: <방송주문> 테이블의 [판매수량], [판매가격] 필드
- 판매금액의 합계 계산
- 사용함수: SUMX

총판매금액 = SUMX('방송주문', '방송주문'[판매수량] * '방송주문'[판매가격])

SUMX(테이블, 식) : 식을 계산해서 테이블에 넣는 식이다. SUM은 단순히 합하고, SUMX는 계산한 뒤에 합한다.

판매금액은 수량 * 가격이므로 위와 같이 나온다.

[B형 - 문제3 - 2 - ①]
▶ <방송주문> 테이블에 새 열 추가
- 열 이름: [거래처]
- 활용 필드: <거래처> 테이블의 [거래처명] 필드
- <방송주문> 테이블에서 <거래처> 테이블의 [거래처명] 필드의 값을 반환
- 사용함수: RELATED

거래처 = RELATED('거래처'[거래처명])

RELATED는 다른 테이블에서 특정 열을 참조할 때 사용한다.

[B형 - 문제3 - 2 - ②]
▶ 측정값 이름: [판매금액PY]
- 활용 필드
  • <방송주문> 테이블의 [총판매금액] 측정값
  • <날짜> 테이블의 [날짜] 필드
- 전년도의 [총판매금액]을 반환
- 사용함수: CALCULATE, DATEADD

판매금액PY = CALCULATE([총판매금액], DATEADD('날짜'[날짜], -1, YEAR))

전년도이므로 -1, year로 계산해야 한다.

[B형 - 문제3 - 2 - ②]
▶ 측정값 이름: [판매금액YoY%]
- 활용 필드: <방송주문> 테이블의 [총판매금액], [판매금액PY] 측정값
- 전년대비 금년도 매출의 비율 반환
- 사용함수: DIVIDE

판매금액YoY% = DIVIDE('방송주문'[총판매금액] - '방송주문'[판매금액PY], '방송주문'[판매금액PY])

전년도보다 몇 퍼센트 상승/하락했는지 나타내는 것이므로 (금년 - 전년) / 전년으로 계산한다. 앞 문제로 나왔던 판매금액PY가 전년도 총판매금액이므로 위와 같은 식이 나온다.

[B형 - 문제3 - 3 - ②]
▶ 측정값 이름: [목표대비총판매비율%]
- 활용 필드: <방송주문> 테이블의 [총판매금액] 측정값
- 목표(대상) 대비 [총판매금액]의 비율 반환 *대상 = 천억(100000000000)
- 사용함수: DIVIDE

목표대비총판매비율% = DIVIDE([총판매금액],100000000000)

목표(대상)의 경우 앞 문제에 천억으로 제시되었다.

 

재무함수 이번에 안 나올 것 같긴 한데 처음 보면 당황할 것 같아서 정리했다. 근데 정리해보니까 나오면 100% 틀릴 것 같다. 이거 태블로 함수 출제범위에는 없는 것 같았다. 태블로로 신청할 걸 그랬나.ㅋㅋㅋ

FV(요율, nper, pmt, pv, 형식)	일정한 이자율을 기준으로 투자의 미래 가치를 계산. 연 이자율 6%, 결제 수 10, 지불 금액 -200, 현재 값 -500,  지불은 기간의 시작일 => FV(0.06/12, 10, -200, -500, 1)
PV(요율, nper, pmt, fv, 형식)	일정한 이자율을 기준으로 대출 또는 투자의 현재 가치를 계산. 매달 말에 500 지불, 지급된 돈으로 얻은 이자율 8%, 20년 => PV(0.08/12, 12*20, 500, 0, 0)
IPMT(요율, per, nper, pv, fv, 형식)	주기적으로 일정한 지급액 및 일정한 이자율을 기준으로 투자에 대해 지정된 기간의 이자 지급액을 반환. 연간 이자 10%, 대출 3년, 대출의 현재 가치 8000 => IPMT(0.1/12, 1, 3*12, 8000)
NPER(요율, pmt, pv, fv, 형식)	정기적, 일정한 지불 및 일정한 이자율을 기준으로 투자 기간 수를 반환. 연 이자율 12%, 기간마다 결제한 금액 -100, 현재 값 -1000, 미래 값 10000, 기간 시작일에 지불 => NPER(0.12/12, -100, -1000, 10000, 1)
PMT(요율, nper, pv, fv, 형식)	원리금 균등 상환 금액 및 일정한 이자율을 기준으로 대출 상환액 계산. 연 이자율 8%, 지급 월 수 10, 대출 금액 10000 => PMT(0.08/12, 10, 10000, 0, 1)
PPMT(요율, per, nper, pv, fv, type)	정기적인 원리금 균등 상환 금액 및 일정한 이자율을 기준으로 투자에 대한 특정 기간의 원금 상환액을 반환. 연 이자율 10%, 대출 기간 2년, 대출 금액 2000 => PPMT(0.1/12, 1, 2*12, 2000)
RATE(nper, pmt, pv, fv, 형식, 추측)	연금 기간당 이자율 반환. 대출 4년, 월별 결제 -200, 대출 금액 8000 => RATE(4*12, -200, 8000)

1회차 시험이라 모의 문제랑 비슷한 수준으로 나올 것 같긴 하다. 필기도 거의 비슷하게 나왔기 때문이다.

수식 문제가 어려우면 어쩔 수 없이 틀리는 거고... 일단 각종 개체 만들고 스타일 지정하는 연습을 많이 해서 빨리 풀고 측정값 문제에 시간을 많이 쏟아야 겠다.