Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Help with GROUP BY and measures (translate from SQL code)

Hello guys,    I need to do something like this SQL code inside POWER BI: Basically I'm converting "facturacion" to negative when certain conditions are met.   SELECT anunciante, SUM( CAS...
Need Help
mwegener's avatar
mwegener
Icon for Most Valuable Professional rankMost Valuable Professional
6 years ago

Hi Anonymous ,

 

check this out.

 

 

Fact. Acumulado =
TOTALYTD (
    SUMX (
        FILTER (
            'Table',
            (
                'Table'[AGENCIA_ORIGINAL] = SELECTEDVALUE ( Dim_agencia[Agencia] )
                    && 'Table'[AGENCIA_MODIFICADA] <> ""
            )
                || 'Table'[AGENCIA_MODIFICADA] = SELECTEDVALUE ( Dim_agencia[Agencia] )
        ),
        IF (
            'Table'[AÑO_CAMBIO] = "2019",
            IF (
                'Table'[AGENCIA_ORIGINAL] = SELECTEDVALUE ( Dim_agencia[Agencia] ),
                'Table'[FACTURACION] * -1,
                'Table'[FACTURACION]
            ),
            IF (
                (
                    'Table'[AGENCIA_ORIGINAL] = SELECTEDVALUE ( Dim_agencia[Agencia] )
                        && 'Table'[AGENCIA_MODIFICADA] = ""
                )
                    || 'Table'[AGENCIA_MODIFICADA] = SELECTEDVALUE ( Dim_agencia[Agencia] ),
                'Table'[FACTURACION],
                'Table'[FACTURACION] * -1
            )
        )
    ),
    DATEADD ( 'Calendario'[Date], -1, YEAR )
)

 

Fact. año completo =
CALCULATE (
    SUMX (
        FILTER (
            'Table',
            (
                'Table'[AGENCIA_ORIGINAL] = SELECTEDVALUE ( Dim_agencia[Agencia] )
                    && 'Table'[AGENCIA_MODIFICADA] <> ""
            )
                || 'Table'[AGENCIA_MODIFICADA] = SELECTEDVALUE ( Dim_agencia[Agencia] )
        ),
        IF (
            'Table'[AÑO_CAMBIO] = "2019",
            IF (
                'Table'[AGENCIA_ORIGINAL] = SELECTEDVALUE ( Dim_agencia[Agencia] ),
                'Table'[FACTURACION] * -1,
                'Table'[FACTURACION]
            ),
            IF (
                (
                    'Table'[AGENCIA_ORIGINAL] = SELECTEDVALUE ( Dim_agencia[Agencia] )
                        && 'Table'[AGENCIA_MODIFICADA] = ""
                )
                    || 'Table'[AGENCIA_MODIFICADA] = SELECTEDVALUE ( Dim_agencia[Agencia] ),
                'Table'[FACTURACION],
                'Table'[FACTURACION] * -1
            )
        )
    ),
    FILTER ( ALL ( Calendario ), Calendario[Año] = MAX ( Calendario[Año] ) - 1 )
)