Calculation

[lucadelicio]

Goodmorning Community,

i need your precious help.

I uploaded my dataset in xlsx.

Is not the complete dataset but is only the rows with FLG_KRI_08 = 1 e FLG_CONSOCIATA = 1 so we have to consider that the original dataset include also rows with these two flags with 0 values.

I need to calculate:

with a selected year NUM_ANNO in the slicer

the count of SKY_KRI_STORICO_VARIANTI for every group of SKY_KRI_CONTRATTO_PASSIVO only for the rows with FLG_KRI_08 = 1 e FLG_CONSOCIATA = 1.

We have to include in this count also the SKY_KRI_STORICO_VARIANTI of previous Years but only for the SKY_KRI_CONTRATTO_PASSIVO of the selected year.

For example:
2021
SKY_KRI_CONTRATTO_PASSIVO = 11287149 = 2
2020
SKY_KRI_CONTRATTO_PASSIVO = 11287149 = 1

I rememeber to pay attention that this dataset is not the complete dataset and it could be present in the original one other rows for every Years with the 2 flags with 0 values.

Thank you in advice for any help.

[Anonymous]

Hi @lucadelicio 

You can create a measure

MEASURE =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[SKY_KRI_STORICO_VARIANTI] ),
    ALL ( 'Table' ),
    'Table'[SKY_KRI_CONTRATTO_PASSIVO]
        IN VALUES ( 'Table'[SKY_KRI_CONTRATTO_PASSIVO] ),
    'Table'[NUM_ANNO] <= MAX ( 'Table'[NUM_ANNO] ),
    'Table'[FLG_KRI_08] = 1
        && 'Table'[FLG_CONSOCIATA] = 1
)

then create a table visual and put the measue to it.

Output

Best Regards!

Yolo Zhu

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

[lucadelicio]

Thank you @Anonymous for your answer.

The formula is good and we are near to the solution. But the problem is that in the total of the rows we are considering also the [SKY_KRI_CONTRATTO_PASSIVO] that are not with the 2 flags = 1.
There is a way to modify the formula considering in the IN VALUES only the [SKY_KRI_CONTRATTO_PASSIVO] with FLG_KRI_08 = 1 and [FLG_CONSOCIATA] = 1?

The result that i need is a table with all the [SKY_KRI_CONTRATTO_PASSIVO] as rows and the total amount of the [SKY_KRI_STORICO_VARIANTI].

Thank you for your help

[Anonymous]

Hi @lucadelicio,

the issue is that the total row doesn't respect the filter on the two flags because Values( ) in the total context includes all contracts, even those not matching the flags.

To fix this

• Isolate only the contracts from the selected year that also have both flags = 1.

• Use that filtered list for both the individual rows and the total.

Regards,

Vinay 

[lucadelicio · Accepted solution]

Thank you for your answer.
The logic it's clear but the formula was difficult to find.
With the help of artificial intelligence i was arrived to the solution with the 

IN

        SELECTCOLUMNS()
option thank you everybody for your help!

COUNTROWS(

    FILTER(

        D_STORICO_VARIANTI,

        D_STORICO_VARIANTI[FLG_KRI_08] = 1

        && D_STORICO_VARIANTI[FLG_CONSOCIATA] = 1

        && D_STORICO_VARIANTI[DAT_LANCIO_RILASCIO] <= MAX('DIM Data Storico Varianti'[Data])

        && D_STORICO_VARIANTI[SKY_KRI_CONTRATTO_PASSIVO] IN

        SELECTCOLUMNS(

            FILTER(

                D_STORICO_VARIANTI,

                D_STORICO_VARIANTI[DAT_LANCIO_RILASCIO] >= MIN('DIM Data Storico Varianti'[Data])

                && D_STORICO_VARIANTI[DAT_LANCIO_RILASCIO] <= MAX('DIM Data Storico Varianti'[Data])

                && D_STORICO_VARIANTI[FLG_KRI_08] = 1

                && D_STORICO_VARIANTI[FLG_CONSOCIATA] = 1

            ),

            "SKY_KRI_CONTRATTO_PASSIVO", D_STORICO_VARIANTI[SKY_KRI_CONTRATTO_PASSIVO]

        )

    )

)

[lucadelicio]

Someone can help me?
I can give you more information if you need.
Thank you