Convert calculated column formula to measure

[Anonymous]

Hi Experts,

I need a help.

The below formula is in calculated column I want to use the same formula in measure. How to convert this as a measure?

Calculated column formula:

Jan = IF(CALCULATE(COUNT(SAP_Data[Period]),FILTER(SAP_Data,SAP_Data[Period] = 1))=0,0,IF(CON_9[Country]="Germany",CALCULATE(SUM(SAP_Data[O_AMT]),FILTER(SAP_Data,SAP_Data[Month]="Jan" && SAP_Data[Country]=CON_9[Country] && SAP_Data[Item_Code] = CON_9[Item_code]))-CALCULATE(SUM(CON_8[Jan]),FILTER(CON_8,CON_8[Country] = CON_9[Country]))+CALCULATE(SUM(TAX[T_AMT]),FILTER(TAX,TAX[Month] = "Jan" && TAX[Country] = CON_9[Country] && TAX[Item_Code] = CON_9[Item_code])),IF(CON_9[Country]="Italy",CALCULATE(SUM(SAP_Data[O_AMT]),FILTER(SAP_Data,SAP_Data[Month]="Jan" && SAP_Data[Country]=CON_9[Country] && SAP_Data[Item_Code] = CON_9[Item_code]))-CALCULATE(SUM(CON_8[Jan]),FILTER(CON_8,CON_8[Country] = CON_9[Country]))+CALCULATE(SUM(TAX[T_AMT]),FILTER(TAX,TAX[Month] = "Jan" && TAX[Country] = CON_9[Country] && TAX[Item_Code] = CON_9[Item_code])),0)))

Measure = ??

Please help.

[Anonymous]

Hi @Anonymous 

Try this one:

if you attach sample data it will be very helpful for us. Though please try this.

Jan =

IF (

CALCULATE (

COUNT ( SAP_Data[Period] ),

FILTER ( SAP_Data, SAP_Data[Period] = 1 )

) = 0,

0,

IF (

FIRSTNONBLANK(CON_9[Country],1) = "Germany",

CALCULATE (

SUM ( SAP_Data[O_AMT] ),

FILTER (

SAP_Data,

SAP_Data[Month] = "Jan"

&& SAP_Data[Country] = CON_9[Country]

&& SAP_Data[Item_Code] = CON_9[Item_code]

)

)

- CALCULATE (

SUM ( CON_8[Jan] ),

FILTER ( CON_8, CON_8[Country] = CON_9[Country] )

)

+ CALCULATE (

SUM ( TAX[T_AMT] ),

FILTER (

TAX,

TAX[Month] = "Jan"

&& TAX[Country] = CON_9[Country]

&& TAX[Item_Code] = CON_9[Item_code]

)

),

IF (

FIRSTNONBLANK(CON_9[Country],1) = "Italy",

CALCULATE (

SUM ( SAP_Data[O_AMT] ),

FILTER (

SAP_Data,

SAP_Data[Month] = "Jan"

&& SAP_Data[Country] = CON_9[Country]

&& SAP_Data[Item_Code] = CON_9[Item_code]

)

)

- CALCULATE (

SUM ( CON_8[Jan] ),

FILTER ( CON_8, CON_8[Country] = CON_9[Country] )

)

+ CALCULATE (

SUM ( TAX[T_AMT] ),

FILTER (

TAX,

TAX[Month] = "Jan"

&& TAX[Country] = CON_9[Country]

&& TAX[Item_Code] = CON_9[Item_code]

)

),

0

)

)

)

[Anonymous]

Hi @Anonymous 

it's not working, getting error on the filter condition.

Regards

Swamy

[Icey · Accepted solution]

Hi @Anonymous ,

Try this:

Jan =
IF (
    CALCULATE (
        COUNT ( SAP_Data[Period] ),
        FILTER ( SAP_Data, SAP_Data[Period] = 1 )
    ) = 0,
    0,
    IF (
        MAX ( CON_9[Country] ) = "Germany",
        CALCULATE (
            SUM ( SAP_Data[O_AMT] ),
            FILTER (
                SAP_Data,
                SAP_Data[Month] = "Jan"
                    && SAP_Data[Country] = MAX ( CON_9[Country] )
                    && SAP_Data[Item_Code] = MAX ( CON_9[Item_code] )
            )
        )
            - CALCULATE (
                SUM ( CON_8[Jan] ),
                FILTER ( CON_8, CON_8[Country] = MAX ( CON_9[Country] ) )
            )
            + CALCULATE (
                SUM ( TAX[T_AMT] ),
                FILTER (
                    TAX,
                    TAX[Month] = "Jan"
                        && TAX[Country] = MAX ( CON_9[Country] )
                        && TAX[Item_Code] = MAX ( CON_9[Item_code] )
                )
            ),
        IF (
            MAX ( CON_9[Country] ) = "Italy",
            CALCULATE (
                SUM ( SAP_Data[O_AMT] ),
                FILTER (
                    SAP_Data,
                    SAP_Data[Month] = "Jan"
                        && SAP_Data[Country] = MAX ( CON_9[Country] )
                        && SAP_Data[Item_Code] = MAX ( CON_9[Item_code] )
                )
            )
                - CALCULATE (
                    SUM ( CON_8[Jan] ),
                    FILTER ( CON_8, CON_8[Country] = MAX ( CON_9[Country] ) )
                )
                + CALCULATE (
                    SUM ( TAX[T_AMT] ),
                    FILTER (
                        TAX,
                        TAX[Month] = "Jan"
                            && TAX[Country] = MAX ( CON_9[Country] )
                            && TAX[Item_Code] = MAX ( CON_9[Item_code] )
                    )
                ),
            0
        )
    )
)

If there are some errors, please give me some screenshots or data sample.

Best Regards,
Icey

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

[Anonymous]

@Icey  

I missed the filter condition. 😊

I think it will work also with MAX(), MIN(), Firstnonblank(), LastNonblank().

[Anonymous]

could you please give sample of your pbix file for hands on.