Getting incorrect Grand Total in matrix

[Anonymous]

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. 

[Anonymous]

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]

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]

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.}

[Anonymous]

Hi,

Thanks for your effort, I tried your above method but still I am not getting the correct value. Also, Apart from Month, I have more slicer to filtered out the data. For eg. Plant, Year and other slicer. I am not getting the correct value using all your methods.

Regards,

Kundan

[Anonymous]

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

[Ritaf1983]

Hi @Anonymous 

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly