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
parry2k's avatar
parry2k
Super User
2 years ago

Gionedis I assume you have sort or key column for each bucket, if not you need to add one and then write following measure:

 

New Measure = 
VAR __MyKey = SELECTEDVALUE ( Test[Key] )
VAR __Table = FILTER ( ALL ( Test ), Test[Key] > __MyKey )
RETURN
PRODUCTX ( 
    __Table, [My Measure] * 1 
) + 0

 

and here is the output

 

 

Gionedis's avatar
Gionedis
Helper I
2 years ago

Hello, i think im foing it wrong: for the bucket i created a column ordering:

 

 

then replicated your dax formula:

 

 

the result was 0:

 

for instance, the roll rate NBV measure is something like this:

 

it goes until the Write-Off

 

Thanks

  • 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