Power BI 보고서 작성 # 2

 

○ 행렬 차트에 당월 누계 , 연간 누계 추가하기

위의 사진과 같이 이전에 수행했던 슬라이서에 따라 해당 연월 기준으로 이전의 데이터만 생성하는 행렬차트에

1. 선택한 연월 기준으로 3개년의 값만 표시

2. 당월 누계 , 연간누계 추가하기

를 수행하였다.

 

먼저 관계가 없는 새로운 테이블을 하나 생성하여 행과 열을 구성하였다.

당월 누계와 연간누계를 새로운 측정값으로 생성하여 값에 추가하면 

 

위 사진과 같이 하나의 열(월)에 두가지 값이 계속해서 나오는 형태를 보이게되므로 

원본 데이터의 MonthNo(1월부터 12월) 에 당월누계와 연간누계를 포함하는 테이블을 생성해야하였다.

따라서 먼저 당월누계와 누적누계 , 연도가 있는 테이블을 생성하였다.

다음과 같이 테이블을 새로 생성하였다.

직접 데이터를 입력하여 테이블을 생성하였으나 이 방법은 좋지 않은 것 같다.

추후에 이후연도에 대한 데이터가 쌓이면 오류가 발생할 것이기 때문이다.

 

그 부분은 나중에 해결하는 것으로 하고,  우선 아래 Dax 식을 통해 새로운 테이블을 생성하였다.

Summarize를 통해 Fact 테이블의 데이터를 Dimension 값 기준으로 그룹화한 테이블에 직접 입력하여 생성한 TB2를 Union을 통해 합쳤다.

summ = 
VAR A = summarize(
                'FACT_InternetSales',
                'DIM_Calendar'[Year],
                'DIM_Calendar'[MonthNo] ,
                "Sum_SA",
                SUM(FACT_InternetSales[SalesAmount])
        )

VAR B = UNION(A, 'TB2')
RETURN B

 

여기에서의 Year와 MonthNo를 통해 행렬 차트의 행과 열을 구성하였다.

 

측정값은 이전에 사용하였던 SelectedMonth_SA를 수정하여 사용하였다.

summ 테이블의 MonthNo가 당월 누계이면 당월 누계를 구하는 계산식을 통한 값을,  연간누계면 연간누계를 구하는 계산식을 통한 값을 반환하고,  그외의 값들은 해당하는 연도와 월에 따라 주문금액(SalesAmount)를 합산한 값이 나오도록하였다.

또한 슬라이서를 통해 선택한 연도와 월을 기준으로 그 이전의 값만 나오고,  3개년의 값만 나오도록 이 모든 것을 중첩 IF 문을 사용하여 작성하였다.

처음에는 Switch문을 사용하였는데 조건(Filter)이 너무 약하게 걸려있어 제대로 원하는 값이 나오지 않는 문제가 발생했다.

 

SelectedMonth_SA = 
    IF(SELECTEDVALUE('summ'[Year]) >= SELECTEDVALUE('DIM_Calendar (4)'[Year]) - 2 
        && SELECTEDVALUE('summ'[Year]) <= SELECTEDVALUE('DIM_Calendar (4)'[Year]) ,
        IF( 
            SELECTEDVALUE(summ[MonthNo]) = "연간누계",
            CALCULATE(
            SUM('FACT_InternetSales'[SalesAmount]),
            FILTER(
                'FACT_InternetSales',
                YEAR('FACT_InternetSales'[OrderDate]) == SELECTEDVALUE('summ'[Year])
                )
                ),
            IF(
            SELECTEDVALUE(summ[MonthNo]) = "당월누계" , 
            CALCULATE(
                SUM('FACT_InternetSales'[SalesAmount]),
                FILTER(
                    'FACT_InternetSales',
                    Month('FACT_InternetSales'[OrderDate]) <= VALUE(SELECTEDVALUE('DIM_Calendar (4)'[MonthNo])) 
                    && YEAR('FACT_InternetSales'[OrderDate]) == SELECTEDVALUE('summ'[Year])
                    )
                    ),
                    IF(SELECTEDVALUE('summ'[Year]) == SELECTEDVALUE('DIM_Calendar (4)'[Year])
                        && VALUE(SELECTEDVALUE('summ'[MonthNo])) > SELECTEDVALUE('DIM_Calendar (4)'[MonthNo]) , 
                        BLANK() ,
                        CALCULATE(
                        SUM('FACT_InternetSales'[SalesAmount]),
                            YEAR('FACT_InternetSales'[OrderDate]) ==  SELECTEDVALUE('summ'[Year]) 
                            &&  MONTH(FACT_InternetSales[OrderDate]) ==  VALUE(SELECTEDVALUE('summ'[MonthNo]) 
                            )
                    )
            )
        )
    ))

 

엄청나게 길어서 한눈에 보이지는 않지만 설명하자면

if문을 통해 먼저 차트의 연도가 슬라이서 선택 연도 기준 3개년만 나오도록 하였고,

그 다음 차트의 월이 연간누계일 경우 연간누계 계산 값을 Calculate( Sum(주문금액) , Filter( 주문날짜의 연도가 차트의 연도와 동일할 때)) 를 사용하여 조건에 맞는 주문금액의 합계를 구했다.

다음으로 당월 누계의 경우에는 Calculate( Sum(주문금액) , Filter( 주문날짜의 월이 선택한 월 이하 일때)) 를 사용하여 구했다.

그 다음으로 차트의 연도와 슬라이서에서 선택한 연도가 동일하고 , 차트의 월이 선택한 월보다 클때 빈값을 Blank()를 통해 반환하고 그 외는 주문날짜의 연도와 월이 차트의 연도와 월과 동일한 경우의 주문금액의 합계를 반환하도록 하였다.

 

그 결과 값은 잘 나왔지만 MonthNo(열) 값의 정렬이 1월부터 12월 , 당월누계 , 연간누계 순서대로 되지 않았다.

이를 해결하기 위해 새로운 테이블 하나를 생성했다.

 

위 테이블 또한 직접 데이터를 입력하여 생성하였다. 

이 테이블을 행렬 차트의 summ 테이블과 MonthNo를 기준으로 일대다 관계를 설정하였다.

이후

Index = RELATED(Index_TB[index])

 

위와 같은 식을 통해 새 열을 생성하였다.

이후에 MonthNo를 Index 열을 기준으로 정렬하면 최종적으로 의도했던 행렬 차트가 완성된다.

 

< 궁금한 점 및 개선할 점 > 

•  직접 데이터를 입력하지 않고 Dax 식을 통해 테이블 생성하는 것

 

•  중첩 IF문이 아닌 Switch 문으로 측정값 구성

@SelectedMonth_SA = 
    SWITCH(
    TRUE(),
    SELECTEDVALUE('summ'[Year]) >= SELECTEDVALUE('DIM_Calendar'[Year]) - 2 
        && SELECTEDVALUE('summ'[Year]) <= SELECTEDVALUE('DIM_Calendar'[Year]),
        SWITCH(
            SELECTEDVALUE('summ'[MonthNo]),
            "연간누계",
                CALCULATE(
                    SUM('FACT_InternetSales'[SalesAmount]),
                    FILTER(
                        ALL('FACT_InternetSales'),
                        YEAR('FACT_InternetSales'[OrderDate]) = SELECTEDVALUE('summ'[Year])
                    )
                ),
            "당월누계",
                CALCULATE(
                    SUM('FACT_InternetSales'[SalesAmount]),
                    FILTER(
                        ALL('FACT_InternetSales'),
                        MONTH('FACT_InternetSales'[OrderDate]) <= VALUE(SELECTEDVALUE('DIM_Calendar'[MonthNo]))
                        && YEAR('FACT_InternetSales'[OrderDate]) == SELECTEDVALUE('summ'[Year])
                    )
                ),
            SWITCH(
                TRUE(),
                SELECTEDVALUE('summ'[Year]) = SELECTEDVALUE('DIM_Calendar'[Year])
                    && VALUE(SELECTEDVALUE('summ'[MonthNo])) > SELECTEDVALUE('DIM_Calendar'[MonthNo]),
                BLANK(),
                CALCULATE(
                        SUM('FACT_InternetSales'[SalesAmount]),
                        ALL('FACT_InternetSales'),
                        YEAR('FACT_InternetSales'[OrderDate]) = SELECTEDVALUE('summ'[Year]) 
                            && MONTH(FACT_InternetSales[OrderDate]) = VALUE(SELECTEDVALUE('summ'[MonthNo]))
                    )
            )
        ),
    BLANK()
)

 

IF( 조건식 , 참일때 값 , 거짓일때 값)

SWITCH( TRUE() , 조건식 , 참일때 값 , 거짓일때 값)  또는 

SWITCH( 값A , 값A와 일치하는지 비교할 값B , 일치할때 값 , 불일치할때 값)

 

•  ADDCOLUMNS 와 SUMMARIZE를 사용한 새 테이블 구성 방법 이해하기
•  Index 테이블을 생성하지 않고 정렬하는 방법은 없을까?

 

 

○ ProductCategory별로 선택한 연도의 데이터를 하나의 개체로 표현

 

이 경우에는 x축에 MonthNo, Y축에 새로운 측정값을 넣고 축소 다중 항목에 나누는 기준( ProductCategory)를 넣으면 된다.

측정값의 경우에는 슬라이서에서 선택한 연도의 SalesAmount가 필요하므로 다음과 같이 작성하였다.

ProductCategory_Selected_SA = 
CALCULATE( 
    SUM('FACT_InternetSales'[SalesAmount]),
    YEAR('FACT_InternetSales'[OrderDate]) == SELECTEDVALUE('DIM_Calendar (4)'[Year]))

 

 

○ 기존의 테이블을 사용하여 새로운 테이블 생성하기

 

- ADDCOLUMNS 사용

 

새 테이블에 컬럼 생성

New_tb = ADDCOLUMNS(DISTINCT('DIM_Calendar'[Month]),"test1","당월누계","test2" , "연간누계")

 

- 여러개의 열들의 값을 하나의 열로 합치기

 

col =

VAR col1 = DISTINCT('New_tb'[Month])

VAR col2 = DISTINCT('New_tb'[test1])

VAR col3 = DISTINCT('New_tb'[test2])

return

UNION(col1 , col2, col3)

 

- ADDCOLUMNS , CROSSJOIN 사용

 

tetest =

    ADDCOLUMNS(

        CROSSJOIN(VALUES('DIM_Calendar'[Date]), VALUES('col'[Month])),

        "@Date" , 'DIM_Calendar'[Date],

        "@Month" , 'col'[Month]

    )

 

동일한 컬럼이 두개씩 만들어진다

이유를 모르겠음

 

- SUMMARIZE , CROSSJOIN 사용

>> crossjoin을 사용하는 것은 비추천한다고 하셨음

 

tetest = SUMMARIZE(

    CROSSJOIN('DIM_Calendar', 'col'),

    'DIM_Calendar'[Date],

    'col'[Month]

)

 

-  SUMMARIZE 사용 

summ =

VAR A = FILTER(SUMMARIZE(

                'FACT_InternetSales',

                'DIM_Calendar'[Year],

                'DIM_Calendar'[MonthNo],

                "Sum_SA",

                SUM(FACT_InternetSales[SalesAmount])

        ), DIM_Calendar[Year] >= 2011)

VAR B = UNION(A, 'TB2')

RETURN B

 

>> 관계가 있을때 사용

가장 이상적인 방법으로 생각됨

SUMMARIZE( 테이블(Fact) , 컬럼(Dimension / 그룹화할 기준) , "새 컬럼명" , 컬럼값 계산식)

Filter( 테이블 또는 테이블을 생성하는 식 , 필터(조건) )

 

 

 

○ 선택한 연도의 월별 누적 주문금액합계 구하기

 

이전까지는 원본테이블을 통해 생성한 관계가 없는 새 테이블로 슬라이서를 만들고 

그에 대한 선택값을 Fact 테이블의 데이터와 Filter를 통해 조건을 걸어주며 연결하는 방식을 사용하였으나

ALL 함수를 통해 기존 Filter를 해제하면 새로운 테이블을 생성할 필요없이 원본 데이터 테이블을 사용할 수 있다.

누적SA = 
    CALCULATE(
        SUM('FACT_InternetSales'[SalesAmount]),
            ALL('FACT_InternetSales'),
            YEAR('FACT_InternetSales'[OrderDate]) == SELECTEDVALUE('DIM_Calendar'[Year])
            && MONTH('FACT_InternetSales'[OrderDate]) <= VALUE(SELECTEDVALUE('DIM_Calendar'[MonthNo]))
        )

 

따라서 위의 측정값을 구하는 식처럼 Fact 테이블의 SalesAmount 합계를

Filter가 해제된 상태에서 새로운 조건(Filter)를 걸어

Fact 테이블의 OrderDate와 슬라이서로 선택한 Dim_Calendar의 SelectedValue를 비교하여 구할 수 있다.

 

○ 연도별 가장 큰 월별 주문금액 합계 구하기

위의 사진에서 왼쪽 테이블과 동일하게 오른쪽의 2012년 월별 주문금액 합계 중 가장 큰 값인 December의 624,502 값을 반환하는 테이블을 만드는 것이다.

왼쪽 테이블은 모든 연도를 선택하였을때의 모습이다.

MaxMon = 
var selected = SELECTEDVALUE('DIM_Calendar'[Year])
VAR a = 
    SUMMARIZE(
        filter(ALL('FACT_InternetSales'),'FACT_InternetSales'[Year] = selected),
        DIM_Calendar[Year],
        DIM_Calendar[Month],
        "MonthSA",
        SUM('FACT_InternetSales'[SalesAmount])
    )
VAR b = MAXX(a, [MonthSA])
RETURN b

 

var를 통해 선택된 연도를 변수로 지정하였다. 이는 생략하고 실제 값을 넣어도 무관하다.

Summarize를 통해 선택된 연도의 월별 주문금액 합계를 a 변수에 지정한다.

 

 

새 테이블으로 selectedvalue를 사용할수는 없으니 임의로 2012의 값을 넣어서 
시각적으로 확인하였다.
슬라이서로 2012년을 선택하면 a 변수에는 왼쪽 테이블과 같이 데이터가 생성된다고 이해하면 좋을 것 같다. 
여기에서 MonthSA의 최대값을 구하면 원하는 값이 나오는 것이다.

 

 

 

 

 

처음에는 이를 sql의 서브쿼리처럼 재사용할 수 있을거라 생각했는데 재사용이 안된다.

또 다른 Summarize에서 a 변수의 "MonthSA" 컬럼값을 사용할 수 없다는 뜻이다.

재사용하려면 Group By를 사용하여야한다.

우선 Summarize만 사용하는 방법으로는 MAXX를 사용하는 것이다.

임의로 생성한 테이블(원본 테이블이 아닌경우)은  MAX를 사용할 수 없다.

위와 같이 해당 연도의 가장 큰 SalesAmount 월별 합계 값을 구하고 Return하면 된다.

 

++ Groupby 사용해서 변경한 식

이전 식을 사용하는게 더 깔끔할 것 같다.

@MaxMon = 
VAR a = 
    SUMMARIZE(
        FILTER(ALL('FACT_InternetSales'), YEAR('FACT_InternetSales'[OrderDate]) = SELECTEDVALUE('DIM_Calendar'[Year])),
        DIM_Calendar[Year],
        DIM_Calendar[Month],
        "SA" , SUM('FACT_InternetSales'[SalesAmount])
    )
VAR b = 
    GROUPBY(
        a,
        DIM_Calendar[Year],
        "MaxSum", MAXX(CURRENTGROUP(),[SA])
    )
return MAXX(b, [MaxSum])

 

○ 추가적인 내용

 

•  GENERATESERIES(1, 5) : 1,2,3,4,5 의 값 반환