Forum Discussion

Gionedis's avatar
Gionedis
Helper I
2 years ago

How to Multiply rows in a table visualization

Hello guys,   i have a table, that the result in percentage is a measure (have column). i want to multiply the first row with all below as follows:   The want column is the multiplication o...
Need Help
Tips and Tricks
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Gionedis ,

 

Please try code as below.

New =
VAR _SUMMARZIE =
    SUMMARIZE (
        ALL ( 'tfat_carteira' ),
        'tfat_carteira'[CC_AGING],
        'tfat_carteira'[AgingValue],
        "Roll Rate NBV", [Roll Rate NBV]
    )
VAR _PRODUCT =
    ADDCOLUMNS (
        _SUMMARIZE,
        "PRODUCT",
            PRODUCTX (
                FILTER ( _SUMMARIZE, [AgingValue] > EARLIER ( [AgingValue] ) ),
                [Roll Rate NBV]
            )
    )
RETURN
    SUMX (
        FILTER ( _PRODUCT, [CC_AGING] = MAX ( 'tfat_carteira'[CC_AGING] ) ),
        [PRODUCT]
    )

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Gionedis's avatar
Gionedis
Helper I
2 years ago

Hello,

 

this time return empty.

 

below the formula:

here the define:

 

DEFINE
    MEASURE '_Medidas'[Roll Rate NBV] = // Calculate the total NBV
VAR cc = CALCULATE(SUM(tfat_carteira[NBV]),tfat_carteira[CC_STATUS_CHANGE] IN {"ROLOU-1-30","ROLOU","ROLLBACK"})

// Get the selected aging value
VAR aging = SELECTEDVALUE(tfat_carteira[CC_AGING])

RETURN
    SWITCH (
        aging,
        "2) 1-30",
            IFERROR(
                cc / CALCULATE(
                    SUM(tfat_carteira[NBV]),
                    tfat_carteira[CC_AGING] = "1) On Date",
                    FILTER(
                        ALL(tdim_tempo),
                        tdim_tempo[INDEX_ANO_MES] = MAX(tdim_tempo[INDEX_ANO_MES]) - 1
                    )
                ),
                BLANK()
            ),
        "3) 31-60",
            IFERROR(
                cc / CALCULATE(
                    SUM(tfat_carteira[NBV]),
                    tfat_carteira[CC_AGING] = "2) 1-30",
                    FILTER(
                        ALL(tdim_tempo),
                        tdim_tempo[INDEX_ANO_MES] = MAX(tdim_tempo[INDEX_ANO_MES]) - 1
                    )
                ),
                BLANK()
            ),
        "4) 61-90",
            IFERROR(
                cc / CALCULATE(
                    SUM(tfat_carteira[NBV]),
                    tfat_carteira[CC_AGING] = "3) 31-60",
                    FILTER(
                        ALL(tdim_tempo),
                        tdim_tempo[INDEX_ANO_MES] = MAX(tdim_tempo[INDEX_ANO_MES]) - 1
                    )
                ),
                BLANK()
            ),
        "5) 91-120",
            IFERROR(
                cc / CALCULATE(
                    SUM(tfat_carteira[NBV]),
                    tfat_carteira[CC_AGING] = "4) 61-90",
                    FILTER(
                        ALL(tdim_tempo),
                        tdim_tempo[INDEX_ANO_MES] = MAX(tdim_tempo[INDEX_ANO_MES]) - 1
                    )
                ),
                BLANK()
            ),
        "6) 121-150",
            IFERROR(
                cc / CALCULATE(
                    SUM(tfat_carteira[NBV]),
                    tfat_carteira[CC_AGING] = "5) 91-120",
                    FILTER(
                        ALL(tdim_tempo),
                        tdim_tempo[INDEX_ANO_MES] = MAX(tdim_tempo[INDEX_ANO_MES]) - 1
                    )
                ),
                BLANK()
            ),
        "7) 151-180",
            IFERROR(
                cc / CALCULATE(
                    SUM(tfat_carteira[NBV]),
                    tfat_carteira[CC_AGING] = "6) 121-150",
                    FILTER(
                        ALL(tdim_tempo),
                        tdim_tempo[INDEX_ANO_MES] = MAX(tdim_tempo[INDEX_ANO_MES]) - 1
                    )
                ),
                BLANK()
            ),
        "8) WOFF",
            IFERROR(
                cc / CALCULATE(
                    SUM(tfat_carteira[NBV]),
                    tfat_carteira[CC_AGING] = "7) 151-180",
                    FILTER(
                        ALL(tdim_tempo),
                        tdim_tempo[INDEX_ANO_MES] = MAX(tdim_tempo[INDEX_ANO_MES]) - 1
                    )
                ),
                BLANK()
            )
    )
    MEASURE '_Medidas'[New] = VAR _SUMMARIZE =
    SUMMARIZE (
        ALL ( 'tfat_carteira' ),
        'tfat_carteira'[CC_AGING],
        'tfat_carteira'[AgingValue],
        "Roll Rate NBV", [Roll Rate NBV]
    )
VAR _PRODUCT =
    ADDCOLUMNS (
        _SUMMARIZE,
        "PRODUCT",
            PRODUCTX (
                FILTER ( _SUMMARIZE, [AgingValue] > EARLIER ( [AgingValue] ) ),
                [Roll Rate NBV]
            )
    )
RETURN
    SUMX (
        FILTER ( _PRODUCT, [CC_AGING] = MAX ( 'tfat_carteira'[CC_AGING] ) ),
        [PRODUCT]
    )

EVALUATE
    SUMMARIZECOLUMNS(
        "Roll Rate NBV", '_Medidas'[Roll Rate NBV],
        "New", '_Medidas'[New]
    )

 

thanks