Forum Discussion

rcardenasbc's avatar
rcardenasbc
Frequent Visitor
2 years ago
Solved

Problem with Total column in visual matrix

I have a problem with a matrix visual in Power BI, the data obtained with a DAX is correct but the Total column only shows December and should total everything, what should I do to make it add up all the months, thanks for the help
I think the problem is the DAX measure which is this:

Proyeccion =
VAR DepartSelect = SELECTEDVALUE(Department[Departamento])
VAR YearSelect = VALUE(SELECTEDVALUE('DAX Calendar'[Año]))
VAR MonthSelect = SELECTEDVALUE('DAX Calendar Aux'[MesNumero])
VAR ActualMonth = MAX('DAX Calendar'[MesNumero])
VAR ActualYear = MAX('DAX Calendar'[AñoNum])

VAR ValorActual =
    CALCULATE(
        SUM(sap[actual]),
        FILTER(
            sap,
            sap[Period] = ActualMonth && sap[Fiscal Year] = ActualYear
        )
    )

VAR ValorPresupuesto =
    CALCULATE(
        SUM(presupuesto[presupuesto]),
        FILTER(
            presupuesto,
            presupuesto[Mes Num.] = ActualMonth && presupuesto[Año Num] = ActualYear
        )
    )

VAR Valor =
    IF(
        ActualYear < YearSelect || (ActualYear = YearSelect && ActualMonth <= MonthSelect),
        ValorActual,
        ValorPresupuesto
    )

VAR Result =
    IF(
        ISBLANK(Valor),
        0,
        Valor
    )

RETURN
    Result
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi rcardenasbc ,

    Based on the description, try to add the following DAX formula.

    VAR Result =
    IF(
        ISBLANK(Valor),
        0,
        Valor
    )

VAR TotalResult =
    IF(
        HASONEVALUE('DAX Calendar'[MesNumero]),
        Result,
        SUMX(
            VALUES('DAX Calendar'[MesNumero]),
            Result
        )
    )

RETURN
    TotalResul

    Viewing the following document to learn more information.

    HASONEVALUE function (DAX) - DAX | Microsoft Learn

     

    Best Regards,

    Wisdom Wu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies