Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Getting incorrect Grand Total in matrix

Hello Community Members,

 

I am facing the issue of incorrect total in matrix table. I am facing with below 'Delta Mix' DAX. Attaching all relevant screenshot. Please suggest me what needs to be changed to correct the value. 'PVM' is my fact table. In data model, there is only one relation i.e, connected to calendar table. 

 

 

Need Help

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous 

     

    Here is my sample data and solution, I hope this helps!

    • Here is my sample table. First, create a calendar table with the following DAX:
    Calendar = ADDCOLUMNS(
CALENDAR(
DATE(YEAR(MIN(PVM[Date])), 1, 1),
DATE(YEAR(MAX(PVM[Date])), 12, 31)
),
"Year", YEAR([Date]),
"Year(Month)", YEAR([Date])*100 +MONTH([Date])
)

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    • Based on your measures, I create the following measures:
    PreviousUnits = 
VAR PreviousYearTable =
SUMMARIZE(
    'Calendar', 
    'Calendar'[Year(Month)],
    "Qty", CALCULATE( SUM(PVM[Qty]), SAMEPERIODLASTYEAR('Calendar'[Date]))
)
RETURN
SUMX(
    PreviousYearTable,
    [Qty]
)
    PreviousSales = 
VAR PreviousYearTable =
SUMMARIZE(
    'Calendar', 
    'Calendar'[Year(Month)],
    "Sales", CALCULATE( SUM(PVM[Sales]), SAMEPERIODLASTYEAR('Calendar'[Date]))
)
RETURN
SUMX(
    PreviousYearTable,
    [Sales]
)
    Price = 
DIVIDE(
    [PreviousSales],
    [PreviousUnits],
    0
)
    _Delta-Mix = SUMX(
VALUES('Calendar'[Year(Month)]),
VAR Diff = [UnitsCurrentYear(Month)] - [PreviousUnits]
VAR UnitPrice = [Price]
VAR UnitPriceTotal = CALCULATE([Price],ALLSELECTED(PVM))
VAR Diff2 = UnitPrice -UnitPriceTotal

RETURN
Diff*Diff2
)
    • Here is my final result:

     

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi,

      Thanks for your solution! Just one thing I want to mention that 'SAMEPERIODLASTEYEAR' consider the complete month of Previous Year. Whereas my requirement is; if I am looking PY sales it should show me sales till 8/2/2024 not complete August!

      Let me know how to modify that

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi,
      One more thing to add. My earlier DAX was giving the correct value but when I am applying the month filter, it's not giving value.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi Anonymous 

         

        I would like to make a sincere apology for the delay.

        Thank you for your clarification. I noticed in your screenshot that there is a hierarchical structure to the product, and your measure “Delta-Mix” may not be calculating the sub-levels and total rows correctly.

        Therefore, I take “UnitPricePVM” “UnitPriceTotal” “Diff” “Diff2”from your “Delta-Mix” measure and adjust them one by one.

        1. The DAX is as below:

        UnitPricePVM = 
SUMX(
    VALUES(PVM[Product]),
    DIVIDE(
    [SalesPreviousYear(Month)],
    [UnitsPreviousYear(Month)],
    0
    )
)
        UnitPriceTotal = CALCULATE([UnitPricePVM],ALLSELECTED(PVM))
        Diff = [UnitsCurrentYear(Month)] - [UnitsPreviousYear(Month)]
        Diff2 = [UnitPricePVM] - CALCULATE([UnitPricePVM], REMOVEFILTERS(PVM[Product], PVM[Product Sub]))

         

        2. Create a new “_Delta-Mix” measure based on the measures from the previous step.

        _Delta-Mix = 
IF(
    HASONEVALUE(PVM[Product]),
    [Diff]*[Diff2],
    SUMX(
    VALUES(PVM[Product]),
    [Diff]*[Diff2]
    )
)

         

        3. Here is my test result. Please refer to the uploaded pbix file. I hope this meets your requirements.

         

         

         

        Best Regards,

        Jarvis Tang

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