Optimizing the Growth calculation DAX

[Vinod_V_12]

Hi Team,

Please could anyone help me in simplying this DAX. This query is consuming a lot of memory in SAP HANA DB and the visuals are not getting loaded.

 YOY Growth % =
VAR b =
    MAX ( 'Table'[Fiscal Qtr Ref] )
VAR Nr =
    SUMX (
        FILTER ( 'Table', 'Table'[Field1] = "ABC" || 'Table'[Field1] = "XYZ" ),
        'Table'[Current Year Sales]
    )
VAR DR =
    IF (
        b <> 0,
        CALCULATE (
            SUM ( 'Table'[Current Year Sales] ),
            FILTER (
                ALL ( 'Table'[Fiscal Qtr Ref], 'Table'[Fiscal Qtr Year] ),
                'Table'[Fiscal Qtr Ref] = b - 4
            )
        ),
        CALCULATE (
            SUM ( 'Table'[Prior Year Sales] ),
            FILTER (
                ALL ( 'Table'[Fiscal Qtr Ref], 'Table'[Fiscal Qtr Year] ),
                'Table'[Fiscal Qtr Ref] = b - 4
            )
        )
    )
RETURN
    DIVIDE ( ( Nr - DR ), DR, 0 )

[Vinod_V_12 · Accepted solution]

I have created two Variables for the script in VAR DR and used the IF condition after the RETURN clause to pull it.

This worked.

[NidhiBhusari]

Hey @Vinod_V_12 can you explain the scenario along with it please share sample data.

[Vinod_V_12]

Sure..

The visual is Matrix. Rows has 8 hierachy dimensions + Column has Fiscal Qtr Year + Values has Sales and Growth % as the fields. The data needs to be shown for 3 Fiscal year both by Quater and Month.

When the Growth% field is added in to the visual it breaks.