Forum Discussion

jit's avatar
jit
Regular Visitor
7 years ago
Solved

Custom tables with custom queries and calculations

Hi, I am trying to convert this excel table calculation into PowerBI but I cant seem to be able to modify the tables extensively and create the desired output of my table. Somehow powerBI can only ha...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi jit 

    Assume you have data as below

    group name value date withdrawal numbers
    G1 withdrawal rate 100 1/1/2019 40
    G2 withdrawal rate 200 1/1/2019 50
    G3 withdrawal rate   1/1/2019 10
    G4 withdrawal rate 300 1/1/2019 50
    G5 withdrawal rate 400 1/1/2019 80
    G1 withdrawal rate 200 2/1/2019 60
    G2 withdrawal rate 300 2/1/2019 50
    G3 withdrawal rate 400 2/1/2019 40
    G4 withdrawal rate 500 2/1/2019 60
    G5 withdrawal rate 100 2/1/2019 30

     

    Please create measures

    YTD =
VAR ytd_value =
    CALCULATE (
        SUM ( Sheet3[value] ),
        FILTER ( ALLSELECTED ( Sheet3 ), Sheet3[group] = MAX ( Sheet3[group] ) )
    )
VAR next_level =
    FORMAT ( MAX ( Sheet3[withdrawal numbers] ) / ytd_value, "0.00%" )
RETURN
    IF (
        ISINSCOPE ( Sheet3[name] ),
        IF ( ytd_value = BLANK (), "0%", next_level ),
        ytd_value
    )


Jan =
VAR jan_value =
    CALCULATE (
        SUM ( Sheet3[value] ),
        FILTER (
            ALLSELECTED ( Sheet3 ),
            Sheet3[group] = MAX ( Sheet3[group] )
                && MONTH ( Sheet3[date] ) = 1
        )
    )
VAR next_level_jan =
    FORMAT ( MAX ( Sheet3[withdrawal numbers] ) / jan_value, "0.00%" )
RETURN
    IF (
        ISINSCOPE ( Sheet3[name] ),
        IF ( jan_value = BLANK (), "0%", next_level_jan ),
        jan_value
    )


Feb =
VAR feb_value =
    CALCULATE (
        SUM ( Sheet3[value] ),
        FILTER (
            ALLSELECTED ( Sheet3 ),
            Sheet3[group] = MAX ( Sheet3[group] )
                && MONTH ( Sheet3[date] ) = 2
        )
    )
VAR next_level_feb =
    FORMAT ( MAX ( Sheet3[withdrawal numbers] ) / feb_value, "0.00%" )
RETURN
    IF (
        ISINSCOPE ( Sheet3[name] ),
        IF ( feb_value = BLANK (), "0%", next_level_feb ),
        feb_value
    )

    Add them in a matrix visual

     

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-juanli-msft's avatar
v-juanli-msft
Icon for Community Support rankCommunity Support
7 years ago

Hi jit 

Assume you have data as below

group name value date withdrawal numbers
G1 withdrawal rate 100 1/1/2019 40
G2 withdrawal rate 200 1/1/2019 50
G3 withdrawal rate   1/1/2019 10
G4 withdrawal rate 300 1/1/2019 50
G5 withdrawal rate 400 1/1/2019 80
G1 withdrawal rate 200 2/1/2019 60
G2 withdrawal rate 300 2/1/2019 50
G3 withdrawal rate 400 2/1/2019 40
G4 withdrawal rate 500 2/1/2019 60
G5 withdrawal rate 100 2/1/2019 30

 

Please create measures

YTD =
VAR ytd_value =
    CALCULATE (
        SUM ( Sheet3[value] ),
        FILTER ( ALLSELECTED ( Sheet3 ), Sheet3[group] = MAX ( Sheet3[group] ) )
    )
VAR next_level =
    FORMAT ( MAX ( Sheet3[withdrawal numbers] ) / ytd_value, "0.00%" )
RETURN
    IF (
        ISINSCOPE ( Sheet3[name] ),
        IF ( ytd_value = BLANK (), "0%", next_level ),
        ytd_value
    )


Jan =
VAR jan_value =
    CALCULATE (
        SUM ( Sheet3[value] ),
        FILTER (
            ALLSELECTED ( Sheet3 ),
            Sheet3[group] = MAX ( Sheet3[group] )
                && MONTH ( Sheet3[date] ) = 1
        )
    )
VAR next_level_jan =
    FORMAT ( MAX ( Sheet3[withdrawal numbers] ) / jan_value, "0.00%" )
RETURN
    IF (
        ISINSCOPE ( Sheet3[name] ),
        IF ( jan_value = BLANK (), "0%", next_level_jan ),
        jan_value
    )


Feb =
VAR feb_value =
    CALCULATE (
        SUM ( Sheet3[value] ),
        FILTER (
            ALLSELECTED ( Sheet3 ),
            Sheet3[group] = MAX ( Sheet3[group] )
                && MONTH ( Sheet3[date] ) = 2
        )
    )
VAR next_level_feb =
    FORMAT ( MAX ( Sheet3[withdrawal numbers] ) / feb_value, "0.00%" )
RETURN
    IF (
        ISINSCOPE ( Sheet3[name] ),
        IF ( feb_value = BLANK (), "0%", next_level_feb ),
        feb_value
    )

Add them in a matrix visual

 

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.