Very high total table results, does not average.

[vicente89]

HELP PLEASE!

Hello, I have a problem that I have been looking for the solution for several days but I can't find it. I would be very grateful if someone could help me, because it is something that I will have to use a lot and I have no way to solve it.

I have a table with data, in which when I make the comparison to see the % that we have gone up or down with respect to last year gives me very high results.

When I do the comparison by date I get the total result very high, but when I do it by week and month I get all the results very high, both the total and the partial results.

What I want is that the total result (like the weekly and monthly ones) gives me the average of all the results that compose it, and it is impossible that these very high numbers that I get are averages.

I leave here below the images in which you can see my case.

I am also going to leave the formula that I am using for it, since I believe that the solution may be that the dax formula that I am using is wrong. The formula is the following:

R_LYDay(B) =

VAR _actual_total =

    SUMX(

        VALUES(calendar_table[Date]),

        SUM(R)

    )

VAR _LY_total =

    SUMX(

        VALUES(calendar_table[Date]),

        CALCULATE(

            SUM(R),

            DATEADD(calendar_table[Date], -364, DAY)

        )

    )

RETURN

DIVIDE(_actual_total - _LY_total, _LY_total, 0)

When I put “R” is where the main measurement is, but for confidentiality I prefer not to share.

I would be very grateful if someone could give me a solution.

Thank you very much in advance.

dayweek

[v-menakakota]

Hi @vicente89 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-menakakota]

Hi @vicente89 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-menakakota]

Hi @vicente89 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[johnt75 · Accepted solution]

You need to add CALCULATE to the code for the actual total. This forces context transition which moves the filter on date from a row context, generated by the SUMX, into a filter context.

R_LYDay(B) =
VAR _actual_total =
    SUMX ( VALUES ( calendar_table[Date] ), CALCULATE ( SUM ( 'Table'[R] ) ) )
VAR _LY_total =
    SUMX (
        VALUES ( calendar_table[Date] ),
        CALCULATE ( SUM ( 'Table'[R] ), DATEADD ( calendar_table[Date], -364, DAY ) )
    )
RETURN
    DIVIDE ( _actual_total - _LY_total, _LY_total, 0 )