Forum Discussion

lucadelicio's avatar
lucadelicio
Impactful Individual
1 year ago
Solved

Calculation

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...
Need Help
  • lucadelicio's avatar
    lucadelicio
    1 year ago

    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]
            )
        )
    )
Anonymous's avatar
Anonymous
Not applicable
1 year ago

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's avatar
lucadelicio
Impactful Individual
1 year ago

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's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      lucadelicio
      Impactful Individual
      1 year ago

      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]
              )
          )
      )