DAX와 파워비아이

전 글에서 VLOOKUP 함수를 대신해 DAX에서 사용할 수 있는 함수 LOOKUPVALUE에 관해 설명드렸습니다.

​

만일 모델 페이지에서 관계 설정이 가능하다면 LOOKUPVALUE라는 함수 대신 VLOOKUP 함수를 대신 할 수 있는 방법이 있습니다.

​

두 쿼리 (Quantity, Type)을 입력한 뒤 모델 페이지로 이동을 하면 Type끼리 자동으로 관계설정이 된 것을 확인할 수 있습니다.

두 쿼리 내에서 같은 같은 데이터를 담고 있는 열은 파워비아이에서 자동으로 인식해서 연결을 시킵니다.

​

이렇게 된다면, 위의 그림과 같이 Quantity라는 쿼리 내의 열 [Type] 과 Type라는 쿼리 내의 열 [Type] 이 서로 상호작용을 하게 되어 각 행에 해당되는 다른 열의 데이터를 가지고 올 수 있게 되는 거죠

​

마지막으로 데이터 페이지에서 '새 열'을 클릭한 후 RELATED 함수를 입력합니다.

아주 간단한 수식이 들어갈 텐데요

RELATED('Type'[Drywall]) 이라고 입력을 하면 엑셀의 VLOOKUP과 같은 결과를 가질 수 있습니다.

​

[Drywall]이라는 새로운 이름을 가진 열에 Type쿼리 내 [Drywall]에 해당되는 데이터를 가지고 오는데 관계가 설정된 열의 데이터가 똑같은 값을 가지고 오라는 수식​이 되는 겁니다.

​

이름을 좀 다르게 만들걸 그랬습니다.

설명을 드리고 나니 이름 때문에 약간 혼란스러운데, 크게 어려운 함수가 아니기에 이해하시는데 큰 무리는 없을 거라 생각합니다.

VLOOKUP 함수도 엑셀에서 많이 사용되는 함수중 하나라 생각됩니다.

다른 시트에 있는 여러 데이터를 쉽게 가지고 올 수 있는 함수입니다.

엑셀에 관한 설명은 넘어가기로 하고, 아래를 먼저 보겠습니다.

먼저 2개의 테이블이 있다고 가정하겠습니다.

하나는 모든 벽의 종류와 각 벽마다 몇 겹의 드라이월 보드가 들어가는지를 보여주는 테이블입니다.

다른 하나는 각 층별 벽의 종류와 각 벽의 수량으로 이루어진 테이블입니다.

여기서 드라이월 보드의 총면적을 구하려면 Quantity * Drywall이라는 수식이 필요하겠죠

​

엑셀이라면 VLOOKUP 함수를 사용해서 가져오면 되겠지만, Power BI에서는 VLOOKUP 함수가 존재하지 않습니다.

역시 비슷한 개념을 가진 LOOKUPVALUE라는 함수를 하용해야 하는데요

첫 번째 테이블 쿼리의 이름은 'Type'이고 두 번째 테이블 쿼리의 이름은 'Quantity'입니다.

그럼 Type 쿼리의 Drywall열의 숫자를 가지고 오는데 Type 쿼리의 Type열과 Quantity 쿼리의 Type열이 일치가 된 행의 값만 가지고 오면 되겠죠?

수식으로 나타내면 다음과 같습니다.

위의 수식을 잠시 설명하자면

Drywall이라는 열을 만드는데 'Type'[Type]과 Quantity[Type]가 일치를 할 경우 'Type'[Drywall]의 값을 가지고 오라는 수식이 되겠습니다.

엑셀의 VLOOKUP 함수와 입력방법이 약간 다르지만 크게 어렵지는 않다고 생각합니다.

​

여기서 전체 Drywall Board의 수량을 구하려면 Quantity열에 Drywall열을 곱하면 나오겠죠?

여기서는 SUMPRODUCT와 비슷한 개념을 가진 SUMX를 사용하겠습니다.

SUMX를 사용하여 Drywall Board의 총면적을 구했습니다.

SUM을 사용하지 않고 SUMX를 사용한 이유는 아래의 Total을 보면 아실 것입니다.

11071*68은 32679가 아닙니다.

왜 그런가는 전에 설명드린 SUM과 SUMX 함수의 차이점을 참조하시길 바랍니다.

​

Total Drywall의 합계를 보여주기 위해 SUM 함수 대신 SUMX 함수를 하용하였으며 아주 간단히 두 테이블을 이용해 Drywall Board의 면적을 계산하였습니다.

​

파워비아이 에서는 LOOKUPVALUE 함수가 엑셀의 VLOOKUP 함수와 똑같은 기능을 한다는 것과, LOOKUPVALUE의 입력방법을 알아두신다면 아주 유용하게 사용될 거라 생각됩니다.

저번 글에서 DAX에는 SUMIF함수가 없다고 말씀을 드렸습니다.

마찬가지로 COUNTIF함수도 존재를 하지 않습니다.

하지만 다른 함수들을 이용해 COUNTIF함수와 똑같은 기능을 가지도록 만들 수 있습니다.

​

아래는 Material이라는 이름을 가진 쿼리이며 각 층별, 타일의 형태별 개수를 보여줍니다.

먼저 COUNT 함수를 사용한다면 행의 개수를 반환하므로 15가 나와야겠죠?

FLOOR열을 기준으로 COUNT함수를 이용해 행의 개수를 구하겠습니다.

Material 쿼리에 있는 [Floor]열의 개수를 반환하라는 함수입니다.

​

그럼 이제 엑셀에서 자주 쓰는 COUNTIF와 똑같은 역할을 하는 함수를 이용할 건데요.

예전에는 CALCULATE 함수 안에 FILTER를 이용해 구했었는데, COUNTX라는 함수를 알게 된 뒤로는 COUNTX만 사용하고 있습니다.

COUNTX는 함수 안에 FILTER를 삽입하여 조건을 만들어줄 수 있습니다.

여기서도 Material 쿼리 내에 있는 [Floor]열의 개수를 구하지만, FILTER를 삽입해 Materila 쿼리의 [Tile]이라는 열에서 "Type A"에 해당되는 행만 카운트하라는 함수가 되겠습니다.

​

그럼 1층에 Type A 만 카운트하려면 어떻게 해야 할까요?

필터가 2개가 필요하겠죠

여기서는 AND를 이용하면 되는데 간단히 &&를 삽입하는 것 만으로 해결이 될 수 있습니다.

편의상 이름을 COUNTXs라고 하겠습니다.

Material[Tile]="Type A" && Material[Floor]="1st"

FILTER 중간에 이렇게 &&를 넣어주면 AND라는 연산자처럼 사용할 수 있으며 [Tile] 열의 "Type A"와 [Floor] 열의 "1st" 에 해당되는 행만 카운트하라는 의미가 되면 결과는 당연히 1이 되겠죠

​

마지막으로 제가 COUNTX라는 함수를 알기 전에 주로 사용하던 함수를 사용하겠습니다.

결과는 COUNTX를 사용할 때와 똑같습니다.

똑같이 필터를 사용했으며 다른 것 하나는 CALCULATE 함수 안에 COUNTROWS를 사용한 것이죠

COUNTROWS는 쿼리 안의 모든 행의 개수를 카운트하는 것이고, 여기에 FILTER를 이용하면 똑같은 결과를 얻을 수 있습니다.

​

행의 개수를 알려주는 COUNT함수에 대해 알아봤는데, 목적지로 운전을 해서 갈 때 여러 가지의 길이 있듯이 똑같은 결과치를 보여주더라도 함수의 사용에 따라 여러가지의 수식이 존재합니다.

딱히 정답이 있는 건 아니므로 각자가 편한 방법을 사용하면 좋을 것이라 생각합니다.

​

하지만 이전의 글과 같이 파워 비아이는 슬라이서를 이용해 필터링 작업을 하는 경우가 많아서 특별한 경우를 제외하고는 그렇게 많이 사용하지 않았던 것 같습니다.

​

하지만 만일 뭔가를 콕 집어서 보여줘야 할 필요가 있다면 알아두면 유용한 수식이 될 거라 생각합니다.

DAX에서 SUMIF함수는 존재하지 않습니다.

사실 보고서에서 슬라이서로 클릭만 하면 원하는 결과를 보여주기 때문에 딱히 필요가 없는 함수 일지 모릅니다.

​

아래와 같은 쿼리가 있다고 가정을 하겠습니다.

여기서 Zone A만의 합계를 구한다고 생각을 하면 엑셀에서는 SUMIF를 사용해서 A의 합계만 계산할 수 있습니다.

하지만 아래와 같이 Power BI에서는 슬라이서를 이용하면 간단히 해결이 될 수 있기 때문에 크게 문제가 되지 않았던 함수였는것 같습니다.

위에 보여지는 슬라이서에서 A를 클릭하면 딱히 수식을 이용하지 않더라도 Zone A의 합계가 자동 계산이 되어 카드로 표시가 되고, 1F를 선택하게 되면 자동으로 1F의 합계를 보여주게 되죠.

​

하지만 어떤 경우는 슬라이서를 사용하지 않고 계산을 해야하는 경우가 생길 수도 있죠.

이럴 때는 계산을 해야 하는데 DAX에서는 SUMIF함수가 존재하지 않는다라는 이야기는 처음에 했습니다.

DAX에서는 CALCULATE함수를 이용하면 해결이 가능합니다.

​

712는 Process의 합을 보여주고, 아래에 149는 CALCULATE함수를 이용해 Zone A만의 합산을 구한 것입니다.

간단히 정리를 하자면 FILTER함수로 Wall 쿼리의 Zone이라는 열에서 "A"만 추출한 Wall 쿼리의 Process열의 합산을 구하라는 식이 되겠습니다.

그럼 조금 전에 슬라이서로 A를 선택했을 때와 똑같은 값은 보여줍니다.

그리고 FILTER는 여러 번 반복해서 사용이 될 수 있습니다.

만약 1F에 있는 Zone A의 합계만 보고 싶다면 아래와 같이 필터를 하나 더 추가하면 됩니다.

SUMIFS = CALCULATE(SUM(Wall[Process]),FILTER(WALL,WALL[Zone]="A"),FILTER(Wall,Wall[Floor]="1F"))

그럼 엑셀에서 SUMIFS와 같은 수식이 만들어집니다.

​

간단히 DAX에서 SUMIF를 계산할 수 있는 CALCULATE함수에 대해 알아보았습니다.

CALCULATE내에는 꼭 SUM이 아니라 다른 함수도 쓰일 수 있기에 여러 함수를 이용해서 수식을 만들어 보는 것도 재미있는 일이라 생각합니다.

엑셀 함수에서 가장 많이 사용되는 함수중 하나가 IF함수가 아닐까 합니다.

DAX에서의 IF함수의 형태는 100% 동일합니다.

딱 하나 다른 점은 셀 대신 열의 이름을 입력해야 한다는 것입니다.

​

IF(조건, 참의 값, 거짓의 값)으로 똑같지만 이전 글에 설명을 드렸듯이 엑셀은 셀 단위로 구성이 되고 쿼리는 열 단위로 구성이 되기 때문에 이것만 다르고 다른 것은 100% 동일합니다.

​

엑셀에서는 IF(C3>500,"높음","낮음")으로 구성이 된다면 DAX에서는 IF('TABLE'[SALES]>500,"높음","낮음")으로 구성이 됩니다.

쿼리의 이름은 ''으로 표시가 되면 열의 이름은 []으로 표시가 됩니다.

그래서 'TABLE'은 쿼리의 이름이며, [SALES]는 쿼리 내 열의 이름이 됩니다.

다시 말해서 TBALE 쿼리 내 SALES라는 열이 500보다 크다면 "높음"으로 표시하고 그렇지 않다면 "낮음"으로 표시해라는 수식이 되는 겁니다.

​

DAX에는 엑셀과 다른 여러 가지 함수가 존재하지만 이처럼 엑셀과 100% 일치하는 수식도 많습니다.

​

이번 글부터는 엑셀에서 많이 사용되는 함수를 위주로 DAX함수와의 차이점에 대해 설명하겠습니다.

아마 엑셀을 사용할 때 가장 많이 사용하는 함수중 하나가 SUM이라 생각합니다.

DAX에서는 SUM외에 SUMX라는 함수를 가지고 있는데 간단히 알아보겠습니다.

​

A,B,C라는 Type의 개수와 단가를 보여주는 표입니다.

그리고 아래에서 Total이라고 보이는 것은 SUM을 이용한 결과치이고 Totalx라는 것은 SUMX의 결과치입니다.

도표상으로는 Total이 맞는 것 같지만 실제의 데이터를 보면 Total은 잘못된 결과입니다.

​

두 함수의 다른 점은 SUM은 수직으로 계산을 하는 반면 SUMX는 수평적인 계산을 한다는 것입니다.

비슷한 엑셀의 함수는 SUMPRODUCT라고 생각을 하면 되겠습니다.

Type A를 예를 들자면 데이터 내부에 있는 모든 A의 개수와 단가를 먼저 곱한 뒤 합계를 계산합니다.

즉 2x34=68, 4x23=92, 4x48=192 그래서 68+92+192=352로 결과를 보여줍니다.

다시 SUM으로 돌아가면 일단 수직으로 개수 2+4+4=10, 단가 34+23+48=105를 먼저 계산한 뒤 개수와 단가의 합을 곱하기 때문에 결과가 다르게 나오는 것입니다.

​

기본적인 수식의 형태는 아래와 같으며

SUM(<column>)

SUMX(<table>, <expression>)

​

여기서는 아래와 같이 수식을 입력하였습니다.

Total = SUM(Sales[Cost])*sum(Sales[EA])

Totalx = SUMX('Sales','Sales'[EA]*'Sales'[Cost])

데이터와 그에 맞는 결과치를 생각하며 SUM과 SUMX 중 맞는 수식을 사용해야 할 것 같습니다.