Wrong total in a matrix

[jlarques]

Hi Team,

Before writing this post, I have been looking for other samples with the same problem, but all solutions didn't work in my case.

I'm working with two simple measures that I want to show in a matrix. The measures are:

Ingresos=SUM(INGRESOS[IMPORTE])

Ingresos LY=

     IF(
         Ingresos>0;

         CALCULATE(

            Ingresos;

            SAMEPERIODLASTYEAR(

                CALENDARIO[FECHA]

            )

        )

    )

The reason that I type an IF expression is to avoid show all rows in the IngresosLY column if the Ingresos column has no values.

The result is this:

As you can see, the total of Ingresos LY is wrong, because the total amount is the monthly amount and not the total of Ingresos LY column.

Do you know where the problem is? Any suggestions?

Thanks, everyone for your help.

José Luis 

[Anonymous · Accepted solution]

Hi,

I try these following measures based on replicate dataset and it seems to work (Sheet1 is just a date + amount column)

1. Actual = SUM(Sheet1[Amount])

2. Lastyear =

IF([Actual] > 0,

CALCULATE([Actual],SAMEPERIODLASTYEAR('Calendar'[Date]))

)

3. TotalForLastyear =

var TT = SUMMARIZE('Calendar','Calendar'[Date],"Lastyear",[Lastyear])

return

IF(HASONEVALUE(Sheet1[Date]),

[Lastyear],

SUMX(TT,[Lastyear])

)

You may want to see more explanation in

https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

[ebeery]

@jlarques this is yet another example of the very common "wrong total" issue.  By definition, the total cell for the "LY" measure does not compute the total of the cells above it - instead it computes the measure in its current context.

In this case, its context is the "sameperiodlastyear" of whatever date context you have applied to the visual. I'm guessing you have a filter/slicer applied to the visual which may be filtering the date context to "January 2021" or something?  If this is the case, one option would be to adjust the upper bound of your date range to include only the date range in 2021 which you want to consider in 2020.

[jlarques]

Hi @ebeery ,

thanks for your answer. 

 Indeed, I have a year filter to visualize the results in the matrix. The problem will be when the matrix grows and includes other years like 2024, 2025, etc. I don't want to adjust the upper bound every time.

I would like to have a simple matrix with the right total for the Ingresos LY measure. With all the power of DAX formulas, I'm sure there has to be a way to be able to display the right total.

Thanks,