Incorrect Totals when using Custom Time Intelligence DAX

[jcantillo11]

Hi All,

I have a DAX measure that essentially calculates a 3 month moving average that is offset by a couple of months. This measure uses the units sold as its base measure and branches out. I am getting the correct figures within the filter context but not in the total. I tried a technique I saw on enterprise DNA but it is not giving me the right answer. Any help would be appreciated and an explanation of the technique would be helpful!

Please see enclosed:

PBI 3Month Moving Avg Testing.pbix (144.3 KB)

Thanks,

Jules

[Harsh]

Hello @jcantillo11,

Thank You for posting your query onto the Forum.

You just need to create a small measure provided below in order to fix the total. Below is the measure as well as screenshot of the final results provided for the reference -

Sales Avg3MOffsetTest - Totals = 
SUMX(
    SUMMARIZE(
        dDate ,
        dDate[Year] ,
        dDate[Month] ,
        "@Totals" , 
        [Sales Avg3MOffsetTest] ) ,
    [@Totals]
)

I’m also attaching the working of the PBIX file as well as providing the link of the post which was created by one of our expert @Greg on this very topic.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Important Note:

1. There’s a difference of $1 in the total between the results showcased into the picture and the results achieved. This must be because of the decimal portion issue.

2. In order to view the entire post, please click onto the link and not onto the “expand/collapse” button.

Thanks and Warm Regards,
Harsh

PBI 3Month Moving Avg Testing - Harsh.pbix (144.8 KB)

Fix Incorrect Totals

When using a measure column in a visual, it is not uncommon in Power BI to find that the totals are incorrect. The Fix Incorrect Totals DAX pattern can be used to correct this issue. When a DAX measure is providing the correct detail value it often, unfortunately, provides an incorrect value for the total row. This is due to there being no evaluation context for the total row. For example, when comparing the [Total Sales] to the [Sales LY] to find the minimum for a customer using the simple DAX…

[jcantillo11]

Hi Harsh,

Thank you for the reply, I did notice that pattern that you provided as the solution in another video posted by EDNA. I think where I went wrong is that I was trying to use the “dDate[Year Month]” rather than the dDate[Year] & dDate[Month] that I was using in the matrix.

Thanks again for your support, you saved me some valuable time!

Jules.

[Harsh]

Hello @jcantillo11,

You’re Welcome.

I’m glad that I was able to assist you.

Thanks and Warm Regards,
Harsh