Forum Discussion

Varpat's avatar
Varpat
Frequent Visitor
3 years ago

INCORRECT MATRIX TOTAL

Hi Everyone,

 

When i used the below dax unfortunately my previous month sales or any time before my selection of may 2023 is not coming

Sum of all sales_Value =
DIVIDE(
    SWITCH(
        TRUE(),
        VALUES('Sheet1'[Currency]) = "USD Dynamic", SUM(V_NIELSEN_FACT_SALES_SUMMARY_TABLE[SALES_VALUE_USD]),
        VALUES('Sheet1'[Currency]) = "Local Currency", SUM(V_NIELSEN_FACT_SALES_SUMMARY_TABLE[SALES_VALUE]),
        VALUES('Sheet1'[Currency]) = "USD Budgeted",
            SUMX(
                V_NIELSEN_FACT_SALES_SUMMARY_TABLE,
                V_NIELSEN_FACT_SALES_SUMMARY_TABLE[SALES_VALUE]
                    * CALCULATE(
                        AVERAGE(DIM_FOREX[FOREX]),
                        USERELATIONSHIP(V_NIELSEN_FACT_SALES_SUMMARY_TABLE[Brand Key Forex], DIM_FOREX[Key])
                    )
            ),
        BLANK()
    ),
    1000000
)
Need Help

1 Reply

  • grazitti_sapna's avatar
    grazitti_sapna
    Super User
    3 years ago

    Hi Varpat 

    You can try using this:-

    Sum of all sales_Value =
    DIVIDE(
    SWITCH(
    TRUE(),
    VALUES('Sheet1'[Currency]) = "USD Dynamic", SUM(V_NIELSEN_FACT_SALES_SUMMARY_TABLE[SALES_VALUE_USD]),
    VALUES('Sheet1'[Currency]) = "Local Currency", SUM(V_NIELSEN_FACT_SALES_SUMMARY_TABLE[SALES_VALUE]),
    VALUES('Sheet1'[Currency]) = "USD Budgeted",
    SUMX(
    V_NIELSEN_FACT_SALES_SUMMARY_TABLE,
    V_NIELSEN_FACT_SALES_SUMMARY_TABLE[SALES_VALUE]
    * CALCULATE(
    AVERAGE(DIM_FOREX[FOREX]),
    USERELATIONSHIP(V_NIELSEN_FACT_SALES_SUMMARY_TABLE[Brand Key Forex], DIM_FOREX[Key])
    )
    ),
    BLANK()
    ),
    1000000
    ) + CALCULATE(
    SUM(V_NIELSEN_FACT_SALES_SUMMARY_TABLE[SALES_VALUE]),
    PREVIOUSMONTH('Date'[Date])
    )

    I hope this will help.
    Thank You.