cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

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

4 REPLIES 4
Community Support

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

Regular Visitor

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.

Regular Visitor

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

Super User

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.

Regards,
Rita Fainshtein | Microsoft MVP

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors